Go from full name(s) in one cell to First Name , MI, and Last Name

[markzullig]

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

[#2]

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

[#3]

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:

[#4]

@markzullig 

Using 365 Insider beta channel:

= REGEXEXTRACT(@fullName, "\w+", 1)

[#5]

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