Forum Discussion
Separate full name field into three separate columns.
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.
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.