Nov 17 2019 12:22 PM
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
Nov 17 2019 01:24 PM - edited Nov 17 2019 01:32 PM
SolutionNov 17 2019 01:29 PM
@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
Nov 17 2019 02:23 PM - edited Nov 17 2019 05:10 PM
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.
Nov 17 2019 03:40 PM
Nov 17 2019 05:09 PM
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.
Nov 17 2019 09:17 PM - edited Nov 17 2019 09:18 PM
@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.
Nov 18 2019 12:26 AM - edited Nov 18 2019 01:27 AM
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.
Nov 17 2019 01:24 PM - edited Nov 17 2019 01:32 PM
SolutionPerhaps the attached file will achieve what you are after.