split name and remove title

[tizwaz]

I have a list of names which I need to split. some of the names have the title eg Mr and others just have first name and last name. eg

Mr John Smith
Mrs Jane Jones
Janet Brown also some say
REF: Joe Bloggs

I just need a list of names without the Title (or the word ref) (would preferably like to split first and last name so I can reverse it but the main thing is to remove the title where it appears. I'm struggling with a formula as I won't be removing the first part of the string in all cases

 

[Gruuuu]

This will work for you:
Step 1:
Select your names, go to Data->Text To Columns: Delimited by SPACE
This splits yours names into columns

Step 2: Enter the titles of your names off to the side somewhere.
Just list them in a column like so

Mr
Mrs
Mr.
Mrs.
REF:
...add in whatever else...

define a named range on this, let's call it "titles"

Step 3: Assuming that data started in Column A Row 1, you should have at least 3 columns of data in A, B, C
Let's put the final data starting in Column G, put this formula in G1:

=IF(ISNA(MATCH($A1,titles,0)),"",A1)

And put this in G2 and G3:

=OFFSET(A1,0,$G1<>"")

And just copy your formulas down to match your data.

If you miss a title, you can add it to your list and redefine your named range.

 

[SkipVought]

Hi,

What application?

This can be a tedious and repetitive task. It takes focused analysis and categorization of your data into similar groupings for the purpose of applying similar parsing.

Do all strings have a title? If not you must identify those with and without titles. Is the title a single word? What is the summary of all distinct titles?

Are there ALWAYS only two names in the string, after the title : no initial, middle name, compound names like Joe Bob Smith or Jack Van Ander? If not you need to categorize the strings accordingly.

Once categorized, you can begin making decisions about how to parse.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tizwaz]

Sorry Excel - although I am going to pull the data into access so the formula could just as easily be in there.

No all strings don't have a title - some are just first name and last name. Most don't have a middle name but one or two might.

If I could just remove the title where there is one that would be better than nothing - haven't tried Gruuu's suggestion yet but will do when I get in work tomorrow

 

[macropod]

Step 2: Enter the titles of your names off to the side somewhere.
Just list them in a column like so

Just so the OP has an idea of what this might involve, see the range of possibilities at:

https://www.britishairways.com/inet/InetControllerServlet.servlet?eId=103001

And don't forget, titles may or may not always be abbreviated ...


Cheers
[MS MVP - Word]

 

[macropod]

PS: How will you know whether a 'Dean' or 'Earl', etc. as the first word in a name is a courtesy title?


Cheers
[MS MVP - Word]

 

[tizwaz]

There are no Dean's or Earl's only Mr, Mrs, Ms or (for some reason) REF: