Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND, CLEAN, REPLACE question
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND, CLEAN, REPLACE question
Works perfectly! Thank you so much!
-- Thank you, scrowley(AT)littleonline.com "Roger Govier" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace macro strange behaviour | Excel Discussion (Misc queries) | |||
Macro: Find and replace | Excel Discussion (Misc queries) | |||
Help! - Lost function in Find & Replace! | Excel Discussion (Misc queries) | |||
Find nth occurrence and replace with ":" | Excel Worksheet Functions | |||
How do I find and replace a question mark in Excel? | Excel Discussion (Misc queries) |