Tech’ top tips – #24. Excel when it comes to data

This tip is provided by the Centre for Enhancing Learning through Technology (CELT).

Currently, my two favourite tools in Microsoft Excel are ‘Text to Columns’ and the ‘Concatenate’ function. Both of these tools really help when we have long lists of data and we need to either sort them or maybe merge data from different cells into one string.

A comma seperated string in ExcelText to Columns enables us to split long strings into individual cells for sorting. For example, a database export containing addresses produces one string per address.

By using Data>Text to Columns we can tell Excel to split the string down using the Commas that appear in the text string.

The Text to Columns wizard

Text to Columns resultWhen the Finish button is clicked, our text string is now displayed across multiple columns – ready for sorting by whichever column we desire.


But what if we want a text string and all we have is data in columns? This where Concatenate comes in.

A basic Concatenate formulaBy typing a formula we can bring data from separate cells into a text string – the key is the ‘&’ symbol.


Basic Concatenate resultsHowever, the formula above will mash all of the data together without any spaces, not very good if we’re going to use this for a mail merge data source or if we need to read it.


Concatenate with spaces addedBy using the speech marks, we can augment the formula to add a space in between the contents of each cell.

Now, our data appears as one string but with the appropriate spacing for reading and using.

One thought on “Tech’ top tips – #24. Excel when it comes to data

  • March 5, 2019 at 2:06 pm

    Just discovered this blog. Wow! I’ve got it to notify me of new posts to get more gems like this. Thanks, Ian!

Comments are closed.