Forum Discussion
markzullig
Sep 09, 2024Copper Contributor
Go from full name(s) in one cell to First Name , MI, and Last Name
Go from full name(s) in one cell to First Name , MI, and Last Name. Also, how to be able to split names as appears in original cell:
Jack A. & Jill B. Smith (Is in one cell & Needs to be split out)
How do I split the above example into First Names, MI, and Last Name Columns?
Thanks
As variant
=REDUCE({"First","Mi","Last"}, names, LAMBDA(a,v, LET( str, TRIM(v), parts, TEXTSPLIT(str, " "), n, COLUMNS(parts), nSep, SUM( (parts="&")*SEQUENCE(,n) ), part, LAMBDA(n, INDEX(parts, n) ), IF( nSep = 0, LET( middle, IF( n=3, part(2), ""), VSTACK(a, HSTACK( part(1), middle, part(n) ) ) ), IF( nSep=2, VSTACK( a, HSTACK( part(1), "", part(n) ), HSTACK( part(3), part(4), part(n) ) ), IF( nSep = 3, LET( middle, IF( n=6, part(5), ""), VSTACK( a, HSTACK( part(1), part(2), part(n) ), HSTACK( part(4), middle, part(n) ) ) ), ) ) ) ) ) )
- PeterBartholomew1Silver Contributor
- Rodrigo_Steel Contributor
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))- Rodrigo_Steel Contributor
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: