Find and Replace

[kabutops]

Hi,

Can anyone help me on this this? I need to replace "St" to Street here is the scenario.

cell1: Dodge St
cell2: Stuart St

when I replace it with Street it will be

cell1: Dodge Street
cell2: Streetuart St

but it should be like this

cell1: Dodge Street
cell2: Stuart Street

Thanks!

 

[JazzSP8]

Hi

Could you use the space to your advantage?

Search for " St" and replace with " Street" (without the quotation marks)

Stuart should be safe then?

HTH

 

[kabutops]

Hi Jazz,

Thanks for the reply. But what if we have an entry of Ken Street when I follow your suggestion it will return Streetreet.

Thanks!

 

[bill chu]

suppose target in cell A1
=SUBSTITUTE(A1&" ", " St "," Street")

 

[kabutops]

Thanks Bill it works!