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

Resources