I have a list of 12K names to parse. All different formats. First Name Last Name, Some have initials some do not, some have suffix and some do not. Additionally, sometime they have 2 first names or 2 last names. Therefore I need to group similar data and review manually for these anomalies, which in my case are more the norm than not. Therefore a straight text to columns won't work well as I will spend more time moving data back together.
I built a formula in B2 for "How many words in each cell" - this is the first part of my approach (used for sort/grouping only)
I now need to see in B3 (or how many other cells are needed), how many characters are in each word. This will allow me to sort by B2 first, and then by B3.
B1 = Cheryl Marie A. Morrow Walsh
B2 = 5 (number of words in B1)
B3 = 6, 5, 2, 6, 5 (this is the # of characters in each word from B1)
Now I can group/sort my data by B2 & B3. This will put all like data together so that I can use the Text to Columns feature on a large group of same format records at one time. I will use the Fixed Width and NOT the delimited. This way I can parse the double first and double last names easily.
I have the formula to count the # of characters in the FIRST word, but then I can't get the formula to work after that. Here is what I am using - =LEN(LEFT(B1,FIND(" ",B1,1)-1)). I tried copying this into B4 and just changing out the [start num] from 1 to a 7 to try and begin counting at the next space / word - But... no luck.
Can anyone offer some help?
Thanks
Bookmarks