FIND, CLEAN, REPLACE question

[Guest]

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.

 

[Roger Govier]

Hi

Try
=IF(ISNUMBER(FIND("Ste",G2)),CLEAN(SUBSTITUTE(G2,CHAR(10),", ")),G2)

 

[Guest]

Works perfectly! Thank you so much!
--
Thank you,

scrowley(AT)littleonline.com

Hi

Try
=IF(ISNUMBER(FIND("Ste",G2)),CLEAN(SUBSTITUTE(G2,CHAR(10),", ")),G2)

--
Regards

Roger Govier

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.