Forum Discussion
Go from full name(s) in one cell to First Name , MI, and Last Name
Single Name formula:
First: =LEFT(A2, FIND(" ", A2)-1)
Middle: =MID(A2, FIND(" ", A2) + 1, 1)
Last: =TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 100)), 100))
Two Full name in one cell:
First1: LEFT(A5, FIND(" ", A5)-1)
Middle1: =MID(A5, FIND(" ", A5) + 1, 1)
First2: =MID(A5, FIND("&", A5) + 2, FIND(" ", A5, FIND("&", A5) + 2) - FIND("&", A5) - 2)
Middle 2: =MID(A5, FIND(" ", A5, FIND("&", A5) + 2) + 1, 1)
Last: =TRIM(RIGHT(SUBSTITUTE(A5, " ", REPT(" ", 100)), 100))
Or,
you can use 'Text to Columns' feature.
Here's how:
1. Select the name/s
2. Goto 'Data, click on 'Text to Columns
3. Choose 'Delimited, then Next
4. Check these: 'Space and 'Other and put "&", and check 'Treat consecutive.... as one, then Next
5. Then on 'Destination section, you can choose where you want to put the split data to appear. In my case I want it on cell B10. then click 'FINISH.
Result:
