Working with text in Excel

Kashflow logo
Share this content

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... 

Please Login or Register to read the full article

The full article is available to registered members only. To read the rest of this article you’ll need to login or register. Registration is FREE and allows you to view all content, ask questions, comment and much more.

About Simon Hurst

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.



Please login or register to join the discussion.

20th Jun 2014 15:19

Text Manipulation

Great stuff, Simon

A simple and concise guide to how Excel manipulates text.

And also useful to see how tables can be used in these scenarios.


Thanks (1)

Related content