VictoriaExcel
New Member
- Joined
- Nov 15, 2018
- Messages
- 14
Hi,
I have a list of thousands of names that need splitting into columns: prefix, first name, middle name, last name and suffix. The full names are varied and therefore the formulas I have tried thus far do not work on the whole list.
Formulas I have tried thus far:
Prefix
=LOOKUP(9.99E+307,SEARCH(Prefix,E7),Prefix)
To look up against a table of possible Prefix (downside being that I may not have captured all possibilities in this table)
First Name
=LEFT(G3,SEARCH(" ",G3))
Doesn't work if the name contains more than one prefix e.g. Professor Doctor Stephen Phillip DUNN
Middle Name
=MID(G3,SEARCH(" ",G3,1)+1,SEARCH(" ",G3,SEARCH(" ",G3,1)+1)-SEARCH(" ",G3,1))
This works in the main
Last Name
=RIGHT(G3,LEN(G3)-SEARCH("#",SUBSTITUTE(G3," ","#",LEN(G3)-LEN(SUBSTITUTE(G3," ","")))))
This picks up suffixes e.g. CBE, DL or last name not one or the other
Suffix
Same issue as prefix and last name
Example names:
<colgroup><col></colgroup><tbody>
</tbody>
I have a list of thousands of names that need splitting into columns: prefix, first name, middle name, last name and suffix. The full names are varied and therefore the formulas I have tried thus far do not work on the whole list.
Formulas I have tried thus far:
Prefix
=LOOKUP(9.99E+307,SEARCH(Prefix,E7),Prefix)
To look up against a table of possible Prefix (downside being that I may not have captured all possibilities in this table)
First Name
=LEFT(G3,SEARCH(" ",G3))
Doesn't work if the name contains more than one prefix e.g. Professor Doctor Stephen Phillip DUNN
Middle Name
=MID(G3,SEARCH(" ",G3,1)+1,SEARCH(" ",G3,SEARCH(" ",G3,1)+1)-SEARCH(" ",G3,1))
This works in the main
Last Name
=RIGHT(G3,LEN(G3)-SEARCH("#",SUBSTITUTE(G3," ","#",LEN(G3)-LEN(SUBSTITUTE(G3," ","")))))
This picks up suffixes e.g. CBE, DL or last name not one or the other
Suffix
Same issue as prefix and last name
Example names:
<colgroup><col></colgroup><tbody> </tbody> Thank you in advance! Any suggestions welcome. Best wishes, Victoria |
<colgroup><col></colgroup><tbody>
</tbody>