There are times when you receive a data set of employee full names in one column and you want to separate the full name into first name and surname in separate columns.

One way is to use the Power Query method, which is great if you have lots of data that gets added each day, week or month and simply want to Refresh your Query to get the output needed each time.

If you want to quickly split a cell’s full name into separate columns on an ad-hoc basis, then the Text to Columns is the way to go.



STEP 1: Highlight your column’s data that has the full names

highlight data


STEP 2: Go to Data > Text to Columns

text to columns


STEP 3: In Step 1 of 3 you need to select the Delimited button and press Next

step 1


STEP 4: In Step 2 of 3, you need to “check” the Tab and Space boxes and press Next

step 2


STEP 5: In Step 3 of 3 you need to press the Finish button

step 3


This will split the column into two columns, separating the first from the second name.  You can go ahead and change the column headings to get the following result:


Text to Columns - Full Names



If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Clear a #REF! Error in Excel When your Excel workbook has formulas that are linked to other workbooks/cells that have been deleted, you will get a #REF! error.This means that the formula refers to a cell that isn’t valid.To get rid of this error message we have to select the cell(s) with this error, ...
Autofill Formulas in an Excel Table One of the advantages of using an Excel Table is the ability to autofill a formula all the way down your data without having to copy and paste.When you write a formula anywhere in your Excel Table, it will automatically fill down and up within that column.As you add extra...
Go To Blanks The Go To Special tool within Excel is a must for any serious Excel user as it has an array of useful spreadsheet formatting and clean up tools.One that I use all the time is the Go To Special > Blanks.  This allows you to delete multiple blank rows/column within seconds. ...
Extract Numbers Using Flash Fill In Excel  Flash Fill in Excel is a new feature that was introduced in Excel 2013.  Flash Fill allows you to combine, extract, move & transform data that belongs in one column, into a new column.One of the cool uses of Flash Fill is extracting the numbers from your text au...