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:
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”.
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”.
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.
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.