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

excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Sep 10, 2024

    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
    Sep 10, 2024

    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
      Sep 10, 2024

      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