Forum Discussion

markzullig's avatar
markzullig
Copper Contributor
Sep 09, 2024

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

  • markzullig 

    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) ) ) ),
              ) ) )
    ) ) )

  • Rodrigo_'s avatar
    Rodrigo_
    Steel Contributor

    markzullig 

    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_'s avatar
      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:

       

Resources