In cases where there is no middle name, the Middle column should be blank.
In cases where there are two middle names, the Middle column should contain both names.
This is the approach explained below.
words) that appear in the full name.
To get a value forfirst, we ask for thefirstvalue inparts.
To get a value forlast, we ask for thelastvalue in parts, by using thecount.
At this point, we have what we need to create the final output for each name.
Bono, Prince, Madonna, Adele, Beyonce, etc.).
Note that we supply the first name and the last name in a normal fashion.
We don’t want to output additional cells for names that have more than one middle name.
the first value in thepartsarray).
The outer DROP function removes the last name fromparts.
The TEXTJOIN function thenconcatenatesthe remaining values inparts, each value separated by a single space.
The result is returned to HSTACK as the middle name.
The same problem occurs if you use Excel’s Text-To-Columns feature.
TEXTSPLIT can split text into rows or columns.
you’re able to use INDEX to retrieve individual values, or entire rows and columns.
COUNTA does not count empty cells.
The number of rows and columns to remove is provided by separaterowsandcolumnsarguments.
Each subsequent array is appended to the right of the previous array….
