Forum Discussion

Michael Turner's avatar
Michael Turner
Copper Contributor
Oct 30, 2017

Formula to move text to col - 4 Words

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 ...
Formulas & Functions
Need Help
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 31, 2017

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.

 

 

Resources