Why use text to columns?

If you’re an Excel user, chances are that at some point in your life, you have come across a spreadsheet stacked full of data that is no good to anyone! In the past, I’ve been quite vocal about the fact that I’m a huge advocate of consistent and tidy data sets that are functional and useful. In order to achieve this, you need to think a little when designing your spreadsheet and break the data down into suitable sections.

This is all well and good if you are creating your spreadsheet from scratch. But what happens when you have inherited a monster of a spreadsheet that is so useless you can’t even sort it correctly because the first and last names have been put into one cell together?! The answer is, of course, you could edit the data manually, add in some columns, split out some data and go through all 10,000 records putting the last name into the cell next to the first name so that you can sort your sheet alphabetically… if you’ve really got nothing better to do!

Another common issue is where you have a spreadsheet that has just one column for the address, with each address line on a new row. Whilst this may look aesthetically pleasing, it causes so many problems if you want to use this data for anything meaningful, such as a mail merge, or to fulfil orders and print labels, or upload it to your CRM system.

But for those of us who value our precious time and can’t afford to spend hours sifting through mountains of data there is exactly a tool for the job that will have this task completed in 2 minutes.

It is quite cleverly named ‘Text to Columns’ and it really does what it says on the tin! It works best with data that is fairly consistent and not too complicated (such as a name which will usually have 2 words with a space in between or addresses with multiple lines) and you do need to make sure that you have enough blank columns for the data once it has been split otherwise it will overwrite the contents of the other cells.

Text to columns is quite a commonly used tool, and you’ll find this function available in both Excel and Google Sheets – the layout may be slightly different, but the steps are exactly the same. The video above will show you how to find the Text to Columns feature in both Excel (both Mac and Windows versions) and Google.

Here’s how to use Text to Columns in Microsoft Excel: 

  1. Start off with your raw data. The first thing you’ll need to do is to highlight the data that needs to be separated.
  2. Select the ‘Data‘ tab and under the ‘Data Tools‘ group select ‘Text to Columns‘.
  3. You will then be presented with the Text to Columns wizard to guide you through the process.
  4. The delimited option is likely to already be checked by default. Check that this is the case then click ‘Next‘.
  5. Next you can choose the criteria (delimiters) for breaking your data down. In simple terms, this is the thing that will indicate to Excel the points at which it should split the data.
  6. In this case we are going to check the ‘Space’ option but if you have different types of data to split, you could also use a comma, semi colon or define a delimiter that is specific to the data you are using.
    Note: This is where you do need some consistency in your data, for example a space between two words, so that Excel can determine how to go about this job . You can also choose to treat consecutive delimiters as one. In most cases you should leave this box checked to avoid your data being split into three columns where there are two spaces between two words.
  7. You will see a preview of your data in the box at the bottom so you can be sure that you have picked the right options. Click ‘Next‘.
  8. The next screen allows you to format your data and decide where to put it. Settings are set to general by default so you can skip this section if you want to, but you also might want to make some general formatting changes now. Ensure that the ‘destination’ box returns the correct cell for where you want your newly split data to be put. When you’re happy, click ‘Finish‘.

E Voila! Your useless and untidy column of jumbled names is now transformed into two neat and tidy columns of first names and surnames, ready to sort, filter, or just look at in amazement, and all in a fraction of the time it was going to take you!

Useful formulas

To substitute line breaks for a chosen character. In this example we substitute it for a comma. Change the part highlighted for whichever character you choose.


Steph Middleton-Foster

Steph has a vision... That every business, regardless of size, should have access to top quality admin support that enables SMEs to maximise their business potential.  She passionately believes that being in business should be fun and that efficient and automated systems are the secret to developing a healthy life-work balance and growing happy & successful entrepreneurs.

She believes that the growing UK virtual assistant industry is a key player in supporting SME growth, in turn encouraging job creation and therefore supporting development of a healthy and vibrant UK economy.

As well as Co-Founder of industry leading network, VA Professional and Editor of VA Pro Magazine, Steph owns and runs multi-award winning VA companies Outhouse-UK and Configure Me This. She is co-author of the #1 best selling book, A Woman's Work and mentors and trains aspiring virtual assistants to build and scale businesses that deliver higher quality support for SMEs. Find out more at www.VAProMag.co.uk