How to divide a combined column using spaces when certain data entries contain internal spaces?

edit

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:

1. Use the RIGHT function to extract the last word (which is presumably the last name) from the combined column. For example, if your combined column is in cell A2, and the last name is separated from the first name(s) by a space, you can use the following formula in an adjacent cell:

=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.

1. Use the LEFT function and the LEN function to extract the first name(s) from the combined column. For example, if your combined column is still in cell A2, and the first name(s) are separated from the last name by one or more spaces, you can use the following formula in another adjacent cell:

=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.