Excel is not all about the numbers.....

What? What do I mean, Excel is not all about numbers? Surely it is the most formidable number crunching tool available? Well, that's debatable, but Excel does more than crunch numbers. It plays with words, and I thought I would share some of my favourite word / text formulae with you.

Before I start, I just want to point out that although these formulae may manipulate text in Excel, you can also use some of them in number formulae, date formulae etc. I'll give you some examples of how, but these little gems can make your life much easier, so take heed.

As we are now in the thick of the 2012 games, I thought I would use some of our data from last week. USA, while I write this blog, has the highest number of medals, so we'll look at their 2008 stats:

Now, imagine you had some inanely boring job where you had to type out how many medals each country had won, by medal type, using this data table. You could type out each sentence or you could use a formula to create the sentences as shown below:

All this formula does is combine text (which is typed in between the quotation marks and highlighted in blue) and the data from various cells (highlighted in red). To combine it all together, we use the & symbol between each different element of the sentence. So in this formula we use the year, the number of total medals and the medal type, and throw some text into the mix to make it sound sensible.

You could also use the concatenate function to do this, but I prefer to just use the &. It makes for shorter, and easier to follow, formulae.

If you're pedantic, like me, and you don't like the fact that the medal type is all upper case, use the PROPER function, as highlighted in red below:

The PROPER function changes text to the Proper format, so each new word starts with a capital letter, followed by lower case. If you want all lower case, use the LOWER function, all upper case, you guessed it, use the UPPER function.

Another handy formula to know for text data is the LEN function. This will tell you the number of characters in a cell. So if the Gold Medal sentence above is in cell A1, the formula =LEN(A1) would give you an answer of 34. Note that spaces are included in the character count.

But my absolute favourite 3 text formulae can be used on one single cell. Imagine Cell A1 contains GOLD 16 USA. You need to split this up between Medal Type, Medal number, and Country. You can do this using the 3 formulae shown below:

These formulae are pretty straight forward. The LEFT function above says, "go to A1, and bring back the first 4 characters". The RIGHT function says, "go to A1, and bring back the first 3 characters, starting from the end and working backwards." The MID function says, "Go to A1, go to the 6th character, and bring back 2 characters".

Now obviously, these aren't ideal to use if your data is constantly changing length, but if you have constant data, these are brilliant. Or, when used in combination with pretty much any other formula, they are more powerful than Michael Phelps himself.

Now for some fun stuff....

A client received a file from a third party in a excel file which had been formatted from a csv file. This file contained product codes, some of which are, for example, 02-10. Now, open any Excel spreadsheet and type this code in. What do you get? Yes, Excel immediately thinks you're typing in a date, and shows you 02-Oct. So you change your number to Text, and Excel kindly gives you the serial number of the date 02-Oct, which is 41184. By which stage you have completely lost your original product code.

I thought, I pondered, I googled, and I couldn't find a quick and easy way to solve this ( if you do know a quick way to solve this, please share as a comment below!). So I came up with the formula below, which gives us the correct answer of 02-10:

I have colour-coded this formula to try simplify it. Starting with the DAY formula, this takes cell A2, and brings back the day from the full date, ie - the 2nd or 2. Using the TEXT formula, I have said take the answer, and format it as a two digit number ("00"), giving us 02.
I did the same with the MONTH formula, which brings back the number month, or 10 in this example. Again, I have set the format to a two digit number. I then inserted a dash into the middle, and hey ho, we have a code 02-10.

This is just one example of how you can combine non-numeric formulas with numeric or date formulae. The possibilities are endless, don't be afraid to experiment with formulae, just keep track of your logic as you go.

In our newsletter this week, we'll look at some more text functions...sign up here.

Have a great week!