Hello and welcome to our new video. In this video we are
going to learn about Substitute Formula. Now the Substitute
Formula can be replaced.
Let's see how to do it.
Now we are going to replace it.
So what we are going to do is we are going to use the
command find and replace.
So what we are going to do is press ctrl plus F and select
replace.
After selecting replace,
I am going to write R and I am going to replace it with P.
And I am going to replace it with
Microsoft Excel cannot find a match.
Because we have not selected it.
Now let's do it.
That's all we have to do.
It has done this one.
It is asking me whether I have to do the next one or not.
So I refuse to do the next one.
So now you will see that it has changed both the P's.
It has converted both the R's.
It has done it in P.
Now suppose I had to convert only one R in P.
I did not have to do the next one.
Then what will we do?
So in this situation, the formula we use is the formula of
substitute.
Now let's see how to use it.
So I will do it as before.
Now what I have to do is the R which is written after or a
better example is in which Shyam Manocha is there.
Here MM is coming.
So what we have to do is we have to convert this to any
other letter.
So let's do it.
So we have to do this.
Here we have to write substitute.
Now it has come.
We have to double click on it.
If you don't want to double click, then you can also use
the tab.
I clicked on the tab.
It is asking me to tell the text.
So in which text do you want to change?
I said I want to change in this text.
Then it is asking me to tell the old text.
And after that new text and
Now what is this thing?
What is this old text?
What do you want to change?
I want to change M.
So I said M.
So you have written M.
Now one more thing to remember is that this formula of
substitute is case sensitive.
Case sensitive means if you write small m here then it will
not work.
Like I have written here.
And after that I want that here the old text will change.
And what is written here?
A Or what is written here?
What can happen?
Let's write A.
I write A here.
I write capital A.
After writing this much, what is the instance number?
Instance number means which M do you want to change?
If we start from here, 1, 2, 3, 4.
So total is 4 M.
So we have to tell which M do you want to change?
Like I said I want to change the second M.
So I did this and entered.
So you will see
Shyam Manocha is writing as it is.
Now what is the reason for writing as it is?
The reason is that we have written small
M.
And what did I say?
It is case sensitive.
If it is case sensitive then we have to understand that if
here in this name's spelling capital M is given then what I
have to do?
I have to use capital M.
Let's do it again.
Now we have to remove small m and put capital M.
Now this work will be done.
So here I will tell you before entering that in Shyam
Manocha this M will be replaced by capital A.
So you will see that capital A is replaced by capital M.
So now you know how to use this formula.
So this is helping us that whatever number of M is written
here it will change only that number which we will tell.
It won't be like find and replace where M is written.
So what else we can do?
Let's see one more thing.
If I don't give this instance number then what will happen?
Let's do it here.
Here I will put substitute formula again.
I have pressed tab.
I have to select tax from here.
I have selected tax here.
After that let's do old tax.
What is old tax? I want to change
R. So I have done
R in capital.
And whom I want to change?
I want to change it from M.
So what I have written here?
I have written M here.
One second.
I have to do it again.
Equals to substitute and here
I have written tax.
After that what is old tax?
I have old tax R.
After that what is new tax?
I have new tax M.
This M
Why it is happening again and again?
It is happening again and again because
I have to give it in double quote.
And second, I don't have to press any button.
So let's try it one more time.
Substitute tax.
I have done this.
After that what is mine?
After that I have old tax.
I have written R in old tax.
Okay.
And R in capital.
After that what I have to do?
I have to write comma and new tax.
M After that I have to give instance number.
First I will show you with instance number.
Then I will remove the instance number.
So I will do it here.
First R is coming here.
And then it is coming here.
It is coming twice in total.
So I want to change the first R or the second R.
So I have put instance number.
So it is Rahul D. Mohilla.
Okay.
Now let's remove the instance number.
Then it will work.
So I have removed it and entered.
So it is Rahul D.
So what did he do?
After removing the instance number, he changed all the R in
M.
Okay.
Apart from this, 2017 2017 If we do it with find and
replace, what will happen?
We know that what will it do?
I will replace 2017 with 2020.
So it will change both.
I did replace.
One second.
I did not select it.
Now let's replace it.
So what happened?
It changed both.
What do we want?
We want one change.
This is the change in the past.
This is the same as in 2017.
I took admission in 2017.
So it will pass out after 3 years.
So what do we do?
Let's close it and do it again.
Now I have to use the substitute formula.
So here I will write equals to substitute and you will see
You have to take this in the whole text.
After that is the old text.
Now he is talking about the text, but the thing that we are
going to change here is written in the number.
So you can't write here directly that you wrote 2017 old
text and I wrote ahead new 2020 and then you put a comma
and wrote the instance number here.
The instance number is that you want to change the other
one.
I put 2 and I entered it.
I took admission in 2017 and passed in 2020.
Now this work is done.
But it is possible that if you have an old version, then it
will not work there.
Because what did he tell us?
He told us to write things in text.
And whenever it comes to text, it has to be written in
double quote.
That is, if you do this here, even if you put it in double
quote, it will still work.
And where you have text, put this habit to write there in
double quote.
Even if it is not a number.
So here we write it.
Now we do enter.
This came.
I took admission in 2017 and passed in 2020.
I hope you understood this.
If there is any doubt in this video, then watch this video
again.
I will meet you in the next video.
Till then keep practicing.