how to delete all words containing specific characters

[andrewpage425]

Hi guys,
I need a formula which can clean up a huge data set. Essentially i need to delete the entire word which contains the characters "aceae". note that "aceae" is a suffix, but i need to delete the entire word not just the suffix, plus keep the rest of the string. i have tried the "find and replace" function of excel with wildcard, but that deletes everything before/after without deleting the entire word. i have tried a combination of formulas to isolate the unwanted words, but that method is inefficient and inaccurate. below is a schematic of what im looking to do:

Column A ------------------------------ Column B
l. planeri asteraceae africa laselva-----> l. planeri africa laselva
l. planeri moraceae europe singer------> l. planeri europe singer
origin l. fluviatilis bignoniaceae asia----> origin l. fluviatilis asia
alternate l. fluviatilis piperaceae asia---> alternate l. fluviatilis asia

thanks in advance!!!

 

[XOR LX]

Hi,

You give only cases in which such a string occurs precisely once, so it's safe to assume that that's always the case:

=TRIM(REPLACE(SUBSTITUTE(A1," ",REPT(" ",999)),FIND("aceae",SUBSTITUTE(A1," ",REPT(" ",999)))-999,1998,""))

Regards

 

[andrewpage425]

Hi,
That worked almost flawlessly. I forgot to mention that in some cells "aceae" does not occur at all. Unfortunately in those instances the formula returns a <#VALUE!> error. Is it possible to have the formula work on cells containing "aceae" yet not return the error for cells that do not contain the target characters?

Sorry for the confusion, and thanks in advance.

 

[XOR LX]

No worries.

=IF(COUNTIF(A1,"*aceae*"),TRIM(REPLACE(SUBSTITUTE(A1," ",REPT(" ",999)),FIND("aceae",SUBSTITUTE(A1," ",REPT(" ",999)))-999,1998,"")),A1)

Regards

 

[Rick Rothstein]

No worries.

=IF(COUNTIF(A1,"*aceae*"),TRIM(REPLACE(SUBSTITUTE(A1," ",REPT(" ",999)),FIND("aceae",SUBSTITUTE(A1," ",REPT(" ",999)))-999,1998,"")),A1)

Regards

I would suggest one modification to your formula so that if it is copied down past the end of current data to account for future additions to the list (or blanks within the list), they display as "" instead of 0...

=IF(COUNTIF(A1,"*aceae*"),TRIM(REPLACE(SUBSTITUTE(A1," ",REPT(" ",999)),FIND("aceae",SUBSTITUTE(A1," ",REPT(" ",999)))-999,1998,"")),IF(A1="","",A1))

 

[XOR LX]

I would suggest one modification to your formula so that if it is copied down past the end of current data to account for future additions to the list (or blanks within the list), they display as "" instead of 0...

=IF(COUNTIF(A1,"*aceae*"),TRIM(REPLACE(SUBSTITUTE(A1," ",REPT(" ",999)),FIND("aceae",SUBSTITUTE(A1," ",REPT(" ",999)))-999,1998,"")),IF(A1="","",A1))

Can't disagree with that.

Regards

 

[andrewpage425]

that did the trick. you guys are fantastic. thank you!!