Removing a set of unwanted characters from a column

If the names are always first and last only separated by a space, i.e. no middle names or initials, no prefixes, no suffixes,... and the superflous text is always separated with a space, the following will work.

=LEFT(A2,FIND(" ",A2,FIND(" ",A2)+1)-1)

Originally Posted by martindwilson

Maybe like this
if there maybe no match wrap in if/isna()
=IF(ISNA(LOOKUP(2^15,SEARCH($E$2:$E$17,$A2),$E$2:$E$17)),$A2,TRIM(SUBSTITUTE($A2,LOOKUP(2^15,SEARCH($E$2:$E$17,$A2),$E$2:$E$17),"")))

Hi Martin

that was extremely helpful, but i seem to be struggling when I apply to my own list of data

I've attached another sample, could you tell me what I am doing wrong? My desired text isnt getting picked up. Is there a limit to how large your list(s) can be?

Thanks again Martin

Originally Posted by jhren

In cell B10 of your 2nd file...

=LEFT(A10,SMALL(IFERROR(SEARCH($E$10:$E$341,A10),LEN(A10)),1))

... and enter as an array formula (Ctrl+Shift+Enter), then fill/copy down.

Also, it appears some of the text you may want to remove is not in your list, e.g. " !".

If you make your list a dynamically-expanding named range, you can substitute its name for $E$10:$E$341 in the formula, and not have to change the formula every time you add to the list.

[ETA] PS: this formula "assumes" the text to remain is always to the left of the text to remove.

thanks for that jhren, the tips certainly helped but im still tripping on something and its eaten up my whole damn night and im ready to give up.

Would you be kind enough to look at it and find out why the formula isn't picking up every unwanted ?

In this updated sample list, the formula isnt picking up the text " (australia) pty ltd !" (which i have highlighted in red in the worksheet so its easy to see), but the problem is that it is only removing the "pty ltd !" part of the cell, and leaving the "(australia)" part behind.

In the unwanted list i also have "pty ltd !" down as an item, and it works fine on that. So maybe it is the parentheses? ive got no idea.

Any guidance would greatly help me in not going mad

Thanks !

Originally Posted by Rob8489

thanks for that jhren, the tips certainly helped but im still tripping on something and its eaten up my whole damn night and im ready to give up.

Would you be kind enough to look at it and find out why the formula isn't picking up every unwanted ?

In this updated sample list, the formula isnt picking up the text " (australia) pty ltd !" (which i have highlighted in red in the worksheet so its easy to see), but the problem is that it is only removing the "pty ltd !" part of the cell, and leaving the "(australia)" part behind.

In the unwanted list i also have "pty ltd !" down as an item, and it works fine on that. So maybe it is the parentheses? ive got no idea.

Any guidance would greatly help me in not going mad

Thanks !

You're not using my formula in your sample. Unlike Martin's formula, mine essentially searches the entire list for the one with the most characters to remove.

In the attached, I converted the list to a Table, added list from previous example and removed duplicates and sorted. The advantage of using a Table is that you can use a structured reference in the formula (meaning you don't have to change it every time you modify the list... similar to a dynamic named range), and it automatically includes new entries at the bottom (you'll see a lightning bolt menu icon which provides options to not expand and stop expanding automatically... just don't use either).

I then used a modified version of my [array] formula...

you're both geniuses! thank you!