FIND, CLEAN, REPLACE question
Hi
Try
=IF(ISNUMBER(FIND("Ste",G2)),CLEAN(SUBSTITUTE(G2,C HAR(10),", ")),G2)
--
Regards
Roger Govier
"SCrowley" wrote in message
...
The Issue:
Cell G2 Has following:
The National Academies [hard return CHAR(10)]
1038 Fifth Street, N.W.
Array Formula in H2 is:
{=IF(ISTEXT("Ste."),CLEAN(SUBSTITUTE(G2,CHAR(13)," , ")),0)}
Currently, it is returning the address as The National Academies 1038
Fifth
Street, N.W.
What I want to do is:
IF G2 has the word "Ste." THEN remove the non-printing character (10),
IF it
DOES NOT contain "Ste." then do nothing.
This may be a Visual Basic script as opposed to a Formula; however, I
feel
it can be done, I'm just missing the mark.
--
Thank you,
scrowley(AT)littleonline.com
|