Search Not Just Numbers

Monday 21 June 2010

Excel Tip: Manipulating text in Microsoft Excel

We all know Excel is great for manipulating numbers but I find I often need to use it to manipulate text, whether that be words or references such as accounts codes. The good news is that Excel has some great formulae for doing this.

I would recommend using the Insert Function (fx) button and looking at the functions in the Text category to see what is available but I will highlight some of the more useful ones here.

The Ampersand

Before we get into any functions it is worth looking at the power of the simple ampersand (&).

You can use the & character to join text together:


="Not"&"Just"&"Numbers"  returns NotJustNumbers
="Glen"&" "&"Feechan" returns Glen Feechan
="The account code you are looking for is "&A2 returns The account code you are looking for is 655-300, where cell A2 contains the Account Code 655-300.


These three functions allow you to return a section of a piece of text.

=RIGHT(A2,x) returns the last (rightmost) x characters in cell A2
=LEFT(A2,x) returns the first (leftmost) x characters in cell A2
=MID(A2,y,x) returns  x characters from cell A2, but starting at the character in position y


=RIGHT("Not Just Numbers",7) returns Numbers
=LEFT(A2,3) returns 655, where cell A2 contains the Account Code 655-300
=MID("Not Just Numbers",4,4) returns Just


This functions returns the length of a piece of text, in characters.

=LEN(A2) returns the length of the text in cell A2


=LEN("Not Just Numbers") returns 16 (NB: Spaces are characters)


These three functions allow you to change the case of a piece of text, great for adding consistency to keyed in data. They allow you to convert to Lower Case, Upper Case and Proper Case (lower case with capitals at the start of each word).


=LOWER("Not Just Numbers") returns not just numbers
=UPPER("Not Just Numbers") returns NOT JUST NUMBERS
=PROPER("NOT JUST NUMBERS") returns Not Just Numbers


Many of these functions are useful for manipulating codes, for example if the first 3 digits of your account code indicate the department, then you can use the LEFT function to strip these out and maybe use the result in an IF formula to determine how that code is treated, or with a VLOOKUP to return the name of the department.

Alternatively they can be used for getting data in a consistent format, for example getting address data all in proper case.

The uses are endless, have a play and see what else you can come up with.

And don't forget those of you who have signed up to Excel Advice by Email can just ask.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report.

No comments:

Post a Comment