Splitting Name and Surname in one column into 2 Excel columns

A common question in Excel is how to split a single column containing a name and surname into 2 columns.

2 main ways of doing this.

For once off conversions, use the Text to Column Tool under the Data tab. Choose Delimited and then specify what is separating the name and surname (typically a space or comma). When you are done it will split the column into 2 columns.

Alternatively if it is a regular exercise, you may want to create formula so that you can copy and paste a column into your spreadsheet and it will instantly split the names and surnames. For this you will need to use the FIND function (to find the splitting point) and then LEFT, RIGHT and/ or MID in order to split the columns.

One problem with both these methods is double barrel names and surnames. This may need to be a manual process where the Data AutoFilter can help


Learn more at our live advanced Excel courses in South Africa. Visit www.AuditExcel.co.za Live Excel Training page