As well as numerical data, text often finds its way into spreadsheets. Simon Hurst explains different ways you can deal with it.
Sometimes, in order to gain access to the numbers, you need to manipulate text information. An obvious example would be a nominal code where different sections of the code represent different aspects of the entity or item.
There are a few ways in which existing text can be manipulated using a manual operation. If the text is part of an external file you can either open the file using File, Open with the Files type set to text files, or use Get External Data, From Text.
It’s worth noting that, although these methods look similar in many ways, there are some important differences. If you open the file directly then changes can be saved back over the original file. In contrast, Get External Data imports the file content into an existing Excel workbook. Also, if you open an existing file with a .CSV (comma separated values) extension, Excel will assume it knows how to deal with the file and will just split the file into separate columns wherever it finds a comma.
On the other hand, if you open a file with a .TXT extension Excel will automatically use the Text Import Wizard to allow you greater control over how your file is imported. Using Get External Data will always invoke the Text Import Wizard for a text file, regardless of the file extension.
If your text data is already in Excel then you can run the Text to Columns Wizard on text in a single column. The Text to Columns command can be found in the Data Tools group of the Data ribbon tab and offers the same options as the Text Import Wizard.
Excel 2013 introduced Flash Fill. This feature attempts to understand how you want to organise your text column by analysing a few examples and then applying the same approach to the rest of the column...
About Simon Hurst
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.