substitute array operaion

[farhan1194123]

i have text in col B1:B101
How can i build the formula in A1 and copy it down
by using substitute function to substitute multiple text at once

==SUBSTITUTE(B1,"1st floor" & "2nd floor"&…So On,"")
OLD TEXT is multiple new text will be ""

 

[JackDanIce]

Can you give a a few examples of before and after? It's not clear what multiple strings you want replaced with a blankstring or how the output should look

 

[FormR]

How can i build the formula in A1 and copy it down
by using substitute function to substitute multiple text at once

Hi, you'd need to nest them, which can quickly become unpractical if you have more than a handful of substitutions.

For example:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"1st floor",""),"2nd floor",""),"and so on","")

 

[farhan1194123]

Can you give a a few examples of before and after? It's not clear what multiple strings you want replaced with a blankstring or how the output should look

there are address in col b1:b101, i want to replace "ground floor" "1st floor" and so on. other items in text string remain
same.
can be done with CTRL+H to search and replace with blank one by one but i want to do with formula.

 

[FormR]

but i want to do with formula.

Hi, do you have anything to comment about post#3