1 | initial version |
If certain data entries in a combined column contain internal spaces, you can use a combination of functions to divide the column using spaces. Here are the steps:
=RIGHT(A2,LEN(A2)-FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
This formula finds the last space in the combined column, and extracts everything to the right of it.
=LEFT(A2,LEN(A2)-LEN(B2)-1)
This formula finds the length of the last name (which is assumed to be in cell B2), subtracts it from the total length of the combined column (A2), and subtracts an extra space to account for the space between the first name(s) and the last name.
Note that these formulas assume that the last name is the last word in the combined column, and that the first name(s) are everything before the last name. If your data is not consistently formatted in this way, these formulas may not work correctly.