Formula to move text to col - 4 Words

[Michael Turner]

I have a process that i need to repeat on a regular basis during data cleansing and that is to separate each title/name in to it's own column. The list contains, first name, last name, and sometimes middle name and sometimes professional title prefixes. I have listed a couple of example of the data i'm working with. Any help would be greatly appreciated.

_____________________________________________________

Associate Professor Johnathan David-Kerigahan

Dr Jing Broadfoot

Mr Mick Ryan

Dianne Franks

____________________________________________________

Cheers Mick

[#2]

Hi Michael,

It's desirably to have some rules (like after the second space from the right is always the name) and/or predefined list of titles, etc. Fuzzy logic is not implemented in Excel directly.

It's bit hard to have common rule for

---

Associate Professor Mr John Lewis Smith Jr

and

John Smith

---

If the rule is what the name has not more 3 separated by space words and before it one or no prefix, prefixes could be defined in separate table and text split based on do we have prefix in the string or not.

Above i assumed the prefix, if exists, is always in first column, first name in second, middle name if any in third and last name in forth one. If doesn't matter - that will be easier.

In general that's good job for Power Query, but could be done by formulas. Here is also better to know is your data organized in tables or for some reasons you prefer not to use them.

[#3]

Michael, just in case here is set of formulas to split names on columns, however no universal one.

https://support.office.com/en-us/article/Split-text-into-different-columns-with-functions-49ec57f9-3...