Excel Tip of the Week #105 - Revisiting Text to Columns

Hello all and welcome back to the Excel Tip of the Week.  It's our second birthday!  Thanks to everyone who has helped over the last two years to keep this blog running, and in particular to my frequent contributing author and general partner-in-crime, Emma Horton.

Although it was said in last week's Excel Community post, I just wanted to add here in ITCounts that, as of last week, I am also no longer blogging as an outside contributor - I've now joined the team here at the ICAEW's IT Faculty, where I am looking forward to progressing the conversation on how IT and the Profession interrelate.

This week, we have a Creator post in which we are looking back at a key Excel tool for fixing input data, the Text to Columns feature.  This was originally covered back in TOTW #28.

What is Text to Columns for?

As the name implies, TTC is a tool for taking a single column of text and chopping it up into several columns.  Most commonly, this is done on the basis of a particular character that's present in the original text.  For example, data from a comma-separated values (.csv) file will often import into Excel as a long string of text with a comma wherever a new field is intended.  TTC would allow you to chop that data up based on where the commas appear.

It isn't just CSV files that can benefit from a TTC approach.  Many external programs will output data in a similar format, with a "break" character being used to indicate column gaps.  The TTC wizard can use any character you want as the key to "break".  For example, spaces or semicolons could be relevant.

How do you do it?

The option for TTC lives under the 'Data' heading on the Ribbon.  Before using TTC, check that your data is suitable, i.e. that:
   - There is a suitable delimiting character; and
   - There isn't any data to the right of the text that could get overwritten by the TTC process; and
   - The delimiting character is only used for delimiting; i.e. that there aren't both spaces between columns and also spaces within data.

Select all the data you want to work on, then press the button.  The TTC Wizard will then walk you through the process.

The first step of the Wizard, shown above, is just establishing what method of separating columns will be used.  The normal method is 'Delimited'.  'Fixed width' will cut at regular width intervals, as if cutting the screen into ribbons; it's rare to see data where this method is appropriate.

The second tab in the Wizard then lets you identify the delimiting character(s).  The most common options are shown in the box, but you can set another character manually if desired.  The 'Text qualifier' option will set a character (here it's ") that demarcates text; within blocks of text, the delimiting character will be ignored.

The third tab of the Wizards allows formatting of the columns prior to operation, but it's usually unnecessary.  From the second tab you can usually press Finish, and get a result like this:

TTC is very fiddly to replicate using formulas / macros; it's a powerful and easy-to-use feature that is well worth remembering for those times where it's needed.

Take a look at the attached file for both the example shown above, and an additional set of data to use as a practice task.

Previous post 
TOTW index
Next post

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.

Text to columns.xlsx