Home // Blog & Excel Tips // Splitting Name and Surname in one column into 2 Excel columns

Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.
2. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!


 

Splitting Name and Surname in one column into 2 Excel columns

Comments Closed
Uncategorised

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