hello and welcome to our new video
in this video we are going to learn
about replace formula
now we have heard about replace
because we have already used it in command
we used to do find and replace
so in excel we see
what is the difference between these two
what can replace command do
and what can replace formula do
so for this i have to open excel
after opening excel
i have some data here
here it is written
bond 0 0 2
bond 0 0 3
here numbers are changed
rest of the numbers are same
till 10
after this
mks 0 0 1
nos 0 0 2
here rest of the numbers are changed
so lets see
how we used to use find and replace
suppose
in this a column
where ever bond is written
i have to replace it
so how i will do that
you have to do simple
press ctrl plus f
after pressing ctrl plus f
you will get a window prompt
what is the replace option
you have to go to replace option
and here it says
find what
after that who to replace
like i say
find bond
and replace it
with james
so i have done this
now what we have to do
we have to replace it
all done
we made 10 replacements
so 10 out of 10
where bond is written
james is there
i will close it
but if we want to do same thing here
but here
the name is different
so how it will be here
so here
what we will do
we will use replace formula
we can't use replace command
like i will show you
how to use replace command
then you will see the problem
so what we do
press ctrl plus f again
and go to replace
you can change anything
you can change only one cell
here mks
xyz
nos
xyz
but if i say
you have to change whole data
then what you will do
because here
all names are same
that's why we replaced it
but here it is different
like suppose
mks
xyz
so all other
characters
name
boxtrm
it will not change
only one replacement
all done we made one replacement
so now we will see
how our replace
formula works
i will close it
and write mks
so now
i will use replace formula
so how to use replace formula
simple we have to put equal sign
and here
replace
double click
here are some arguments
first argument is
old text
so i have to put that text
so i said this is the text
so i took it
then here
comma
start number
it is asking
from where you want to start
in last video we saw
length function
from which we can find
text length
let's count
1,2,3,4,5,6
total 6
it is asking start from where
we said start from m
because we want to change mks
so we gave
start number
after that
number of characters
how many characters
you want to change
i said 1,2,3
i want to change 3 characters
so i wrote 3
after that i put comma again
now it is asking
new text
what you want to write instead of mks
we said x,y,z
so i wrote
x,y,z
capitalized
always remember
whenever you write
text
double quote
like we did here
we directly selected cell
then you can do
but if you write
x,y,z
you have to double quote
without double quote
let me close this
so what is this
?
so it doesn't know what to do
so here again
x,y,z
in double quote
now i will enter
and you will see
x,y,z
after that
if i apply it
so here
x,y,z
0,0,3,0,4,0,0,5
now
you see
when we were using replace command
but now we are using
replace formula
now it is not like
suppose
if you want to change middle part
like here 0,0,0
it starts from 0,0,1
some one said
don't start from 0,0,1
do 1,0,1,1,0,2
then what we have to do
can we change things in mid
yes we can
our formula works in mid
so what we have to do
I have to write equals to
I have to write same thing again
double click it
then again it is asking old text
it is asking start number
now count 0
m is on 1st number, k is on 2nd number
s is on 3rd number and 0 is on 4th number
so I wrote 4th
then I have to write comma again
number of characters
I have to change only one character
I have to write 1 instead of 0
I will put 1 in number of characters
after putting 1 I wrote comma again
and last argument is new text
what do you want to put in new text
I said I have to put only 1
now tell me should I write it in double quote
or not, let's see
does it show any error or not
so we entered it and mks101
double click it
102, 103, 104, 105
106
now your question will be why we did not put it in double
quote
because we always put text in double quote
we do not put number
what is 1, it is number
and when we were changing mks or mb
you can say
it is single digit
so it is single digit
if I want to change k
so it is possible that it works on k
we wrote equals to replace
and after replacing
what is old text, it is my old text
after that I said start number
I have to change k
I said I have to change only 1
and after that what is new text
I want to write r
so I wrote r
now see r
one second
now I am writing in double quote
and it is showing me
there is a problem with this formula
if I put double quote
will it work
here I put double quote
now I enter
now you will see mrs001
and where ever it was x
it will be r
double click it
nrs, ary, brx, trm, xrr
frx, mrc
so it is clear
if I have different values
how can I change them
I will use replace formula
I hope you understood
if you have any problem
play this video again
I will meet you in next video