ICAEW.com works better with JavaScript enabled.

Tip of the Week

Excel Tip of the Week #392 - Text to Columns redux

Author: David Lyford-Smith

Published: 04 May 2021

Hello all and welcome back to the Excel Tip of the Week. This week we have a Creator level post in which we’re taking a definitive revisit to the handy data transformation tool, Text to Columns.

What is this feature all about? How do you use it?

Text to Columns is a very long-standing data transformation tool that can help parse data in one column and separate it into several. It’s particularly commonly used when dealing with data imported from other programs, for example comma-separated values.

For an example, let’s take a look at this sample sales data:

Screenshot

We can convert this single-column data by selecting it and then using the Text to Columns Wizard, which is accessed from Data => Text to Columns:

Screenshot

The options here are for how the new columns will be created – “Delimited” means that each column is marked by some particular character, and “Fixed width” is for cases where the data has been lined up visually and essentially needs cutting into strips. Here we are dealing with delimited data.

Screenshot

On the left here we can see where we identify which character is the delimiter. A selection of common options is given, or we can specify our own. This example also shows us how we can use the “text qualifier” field: In our original data, some of the customer names – e.g. ABC, Incorporated – include commas. Normally this would cause a problem, as the two parts of the name would get separated – but in our case, the customer names are all wrapped in quotation marks. By setting the quotation makes as a text qualifier, we can tell Excel to ignore any delimiter characters between them. We can see in the live preview that this will get us the result we want.

Most of the time you can just stop here and press Finish, but for completeness’ sake here’s the final step in the Wizard:

excel

If you do use this step, you can specify a format for each new column that will be created. You can also use the “Destination” box to specify a location for the new columns – by default they are placed over the top of the original data, overwriting it, but if you prefer you can place them elsewhere and retain the original data as well.

The Advanced option here is interesting to consider:

excel

This lets you control how Excel interprets numeric data – useful if e.g. you have been supplied European number format data such as 4.123,06 and want to have Excel convert into UK / US standard. It can also handle trailing minus signs to convert e.g. 4,123.06- into a proper -4,123.06.

Once you are all done, Finish will output the completed data:

excel

This lets you control how Excel interprets numeric data – useful if e.g. you have been supplied European number format data such as 4.123,06 and want to have Excel convert into UK / US standard. It can also handle trailing minus signs to convert e.g. 4,123.06- into a proper -4,123.06.

Once you are all done, Finish will output the completed data:

Note that Text to Columns is a programmatic step – it does not generate formulas or any kind of audit trail. If you are worried about errors in the transformation process, use the Destination box as described above to output the separated data separately from the original data.

Additional quirks

It’s worth noting that you can do essentially the reverse of Text to Columns using the TEXTJOIN function:

excel

You can also automate the process of taking a set of data and splitting it using Power Query’s Split Column transformation:

excel

This has the advantage of being repeatable if you paste in new data later on.

Finally, you might be wondering if it’s possible to split data this way formulaically – the answer is “No” with basic formula options, but Google Sheets does have a SPLIT function that can do it – although without the ability to declare a text qualifier:

excel
You can check out all the examples from this blog in this file and this Google Sheet.
Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250