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.

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

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

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

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

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