Excel – How to separate first and last name into separate columns

If we want to split data in one cell into many columns, we can use the built-in Excel tool, which is called “Text to Columns”. The most common problem is the desire to separate the name and surname in one column and here I will show how we can do this.

Sample data below:

first and last name in the Excel spreadsheet

In this sheet, the name and surname are separated by a comma followed by a space. We select the data and go to the “Text to Columns” tool in the “Data”.

data next step text to columns

We can separate the data in two ways, “Delimited” in this case there is a separator between the data, eg comma, space, period or any other character. The second option is “Fixed width”, where the division is based on the defined column width. In our case, we have a separator, so we use “Delimited”.

excel convert text two column wizard

In this window we specify what separator separates our data, in this particular case we select “Comma” and “Space”. We can immediately see how the selected separator will affect the separation of data.

we divide the name and surname into two columns excel

And here is the effect of our division of the text into two separate columns. This function is very useful as we haven’t to edit several dozen or several thousand records.

separating text into separate Excel columns