Finding from a List

[levy8450]

I've got a simple sounding problem. I have a list of common name suffixes (e.g. Jr., Ph.d, J.D., etc). I have a long list of full names. I want to search and see if any of the suffixes on my list are in the name. If so, I want to parse the name into part 1 (everything but the suffix) and part 2 (just the suffix).

I can do all the parsing, but I can't think of a formula that will look at the name and tell me if any of the suffixes are in the name, and which one.

Of course I can brute force it by creating a massively long formula that searches for each term, but I want to try to use a formula that uses a list.

Any solutions?

Thanks.

 

[hiker95]

levy8450,

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Or, if your file does not contain sensitive information, you can upload it to www.box.net and provide a link to your workbook.

 

[hkaplan2]

Tough on a number of levels. For starters, how clean is the original list? Can it include PHD and PHd, MD and M.D.? You might have to include many combinations for the same suffix in order to find them all. And how is the suffix delimited? Comma, space, or what?

You can try filtering using "contains", or the RIGHT() command to find the right x number of characters, but that would also be inconsistant.

You could use text to columns to split the field into columns using space as a delimiter, and sorting by the right column. Problem here is names like Mary Ann Smith PHD, which will throw the suffix into the the 4th column while John Jackson MD will throw the suffix into the third column.

 

[levy8450]

The list has about 50 suffixes, and every time a new one comes up, I can add it. So I'm not worried about having Ph.D but not PhD etc.

If I have a name like "Mr. Alan Louis MD" what I would like is to look through my list of 50 suffixes and find that the name includes one of them - MD. Then I can parse accordingly.

It does get complicated in that some suffixes can have a space in the suffix, such as "M.S. Ed." So I can't just look at the last set of characters after the last space.

I realize that Excel may not be able to do this, and also that there are a number of workarounds, like having lots of columns or one massive formula.