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

Replace Excel Formatting with Another Formatting Imagine this, you have a table full of bold text.  The bold text could also be all over your worksheet in random cells. Then you decide that the bold text does not suit your expected design and prefer red colored text instead. What would you do? Changing all of the forma...
Rank Smallest to Largest With Excel Pivot Tables   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 RANK SMALLEST TO LARGEST calculation. This option will immediately calculate the rankings (1 being the smallest value) for your values, allowin...
Add Leading Zeros in Excel Do you have a lot of numbers with an uneven number of digits in your Excel list? Do you want to make them uniform by adding leading zeros to them? Well, it's a pain to add zeros in front of them one by one! Thankfully, Excel allows you to add leading zeros with one singl...
Excel’s Subtotal Feature Excel´s Subtotal feature is a great way to automatically insert a Sum/Count/Average/Max/Min subtotal to your data set with a press of a button. This feature is located under the Excel Data tools menu: Data > Subtotal.  To insert this feature you need to follow these quick s...