Replacing specific characters with spaces

N

Night Owl

In the same address list, I would also like to replace the underscore
character with a space. The number of these characters vary from address to
address, so could I use the FIND function or would it need to be more
complicated than that?

Thanks again,

Pete
 
M

Max

Try in say, C1: =TRIM(SUBSTITUTE(B1,"_"," "))

where B1 contains the earlier formula:
=IF(ISNUMBER(SEARCH(",",TRIM(A1))),TRIM(MID((TRIM(A1)),SEARCH(",",TRIM(A1))+
1,99)),TRIM(A1))

Copy C1 down

Or, collapse the 2 formulas into one, i.e. put instead in B1, copy down:
=TRIM(SUBSTITUTE(IF(ISNUMBER(SEARCH(",",TRIM(A1))),TRIM(MID((TRIM(A1)),SEARC
H(",",TRIM(A1))+1,99)),TRIM(A1)),"_"," "))
 
N

Night Owl

Thanks for your replies, guys. I've managed to sort this, now. Thank you.

You wouldn't mind having a look at my next post, would you :)

Peter
 
Top