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

Top Excel Data Cleansing Techniques Data cleansing is an important activity within Excel and one that we find ourselves doing day in day out, sometimes without even knowing it.So what is data cleansing?In the words of Oz du Soleil, Excel MVP, it is like "Hercules being sent out to capture the three-he...
Excel Table: Styles There are lots of different Table Styles that you can choose from to spice up your Excel Table.  You can also customize your own style if you don't like any of the default Excel styles.To access the Table Styles, you simply need to click inside your Table and then the Table T...
What Microsoft Excel Version Do I Have? We know that Microsoft Excel has different features across different versions and there are several Excel version, like Excel 2003, 2007, 2010, 2013 and 2016!So whenever I use Microsoft Excel I want to know right away which Excel Version I am using.  And boy, do I get confuse...
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...