Separate full name field into three separate columns.

[mnelsonhd]

I'm trying to separate the first,middle and last names into separate columns. The problem I'm running into is that some full names contain a "." with the middle initial and some do not and others simple have no middle initial at all.

Thank you in advance for your time and consideration.

Sample file attached.

Mn

[#2 · Accepted solution]

@mnelsonhd 

Perhaps the attached file will achieve what you are after.

[#3]

@mnelsonhd Hi. Looks like you have some which contain a middle initial and some which don't. The way I'd handle this is to process the name separating the parts by using the spaces as a separator. Where the name is in three parts (containing a middle initial), process one way, and for those without, process a different way. If all of the names are in the first column of the sheet and there are no empty rows in the middle, something like:

Function SplitName()

Dim names() As String

For i = 1 To Rows.Count

If Cells(i, 1).Value = "" Then
' No more data to process
Exit Function
Else
' Process the cell
names = Split(Cells(i, 1).Value)
' If it's two names, put into first and last name
If UBound(names()) = 1 Then
Cells(i, 3).Value = names(0)
Cells(i, 5).Value = names(1)
Else
' Otherwise, put the middle initial or name into the middle cell
Cells(i, 3).Value = names(0)
Cells(i, 4).Value = names(1)
Cells(i, 5).Value = names(2)
End If

End If

Next i

End Function

[#4]

@Riny_van_Eekelen 

WOW! works like a champ. Now that the name has been solved I have one last issue..... in the office/company field some company names also contain the contact phone number in the same field vs the phone column. Lastly the email column contains an "A" after the email address making them impossible to use.

Again, Thank you in advance as you've saved countless hours searching the internet and making corrections to over 2k of names!!!

New file attached.

[#5]

Hello,

You can use the Flash Fill to extract (separate) and join (combine) first, middle and last name

Example:
Assume you have Full Name in column A1 such as Abiola David Smith where Abiola is the First Name, David is the Middle Name and Smith is the Last Name
In column B, type is Abiola and click Enter. Then execute CTRL + E. All the First Name in column A will be extracted. Then you can do the same for Middle Name and Last Name. So easy

On the other hand, you can use LEFT, MID/FIND and RIGHT functions to extract values.

Regards

[#6]

@Abiola1 

Thank you for the help. Unfortunately the solution provided wasn't able to completely work without errors. I've tried several times but for whatever reason it will randomly add a letter here and there.

[#7]

@mnelsonhdthe formula in the attachment may help. you may use the number of spaces in a cell as an indicator of the number of words.

[#8]

@mnelsonhd 

Rather than creating one huge formula to do the job, I prefer to break the problem into smaller pieces.

Your data for Name, Phone and E-mail is indeed quite dirty and not consistent. The only consistency I find in your example is that phone numbers are always 12 characters long (10 digits plus 2 separators).

The email adress always seems to be in its own column, so I'll deal with that separately.

The attached workbook describes the four steps needed to clean-up the data.

Step 1: Get your original data (highlighted yellow)

Step 2: TEXTJOIN the first two columns

Step 3: Split Name and Phone# using LEFT, RIGHT and LEN

Step 4: Get rid of the " A" in the e-mail addresses and get rid of trailing spaces

A last step you may want to perform yourself is to do replace the "." in the phone numbers by "-". 

Now, I assume that this is not a process you will have to go through on a daily basis. If it is, you may need find a more automated solution.