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

Excel Table: Row Calculations One of the most powerful feature of an Excel Table is the use of formulas to calculate its Column contents.  To activate this you need to click in your Table and go to the Table Tools tab in the Ribbon.  From there you simply need to activate the Total Rows check box. This wil...
Show The Percent of Row Total With Excel Pivot Tab... Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF ROW TOTAL calculation. This option will immediately calculate the percentages for you from a table filled with numbers such as sales data, exp...
Show The Percent of Difference From Previous Years...   I am sure that your boss has asked you to come up with a Year on Year variance report at some stage.  There are a couple of ways to get him/her an answer. One is using Formulas, but that will take time to set up and you are exposed to errors! The other method is t...
Summarize Data With Dynamic Subtotals What does it do? It returns a Subtotal in a list or database Formula breakdown: =SUBTOTAL(function_num, ref1) What it means: =SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data) ***Go to the bottom...