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