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)),"_"," "))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Night Owl" wrote in message
...
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
|