Excel: Text Functions (Part 1)

Whenever people think of Excel they think of numbers and calculations. However, there is a wide selection of functions that allow you to work with and manipulate text. Perhaps not something you would normally consider doing when working with Excel but you’d be surprised how useful these functions can become.

Here is s selection of text functions I have found useful over the years, dealing with a variety of problems and situations.

CLEAN

Syntax : =CLEAN(cell ref)

This removes any non-printable characters. Seems like an odd thing, but I have found this to be quite a common problem when importing data from other systems such as SAP/Oracle etc. into Excel. These systems tend to export data in .csv format and sometimes directly to Excel but you will often find what look like spaces at the beginning or end of a text string or set of numbers. So you try using the TRIM function (see further on) to remove spaces but that doesn’t work as they are not ‘spaces’, and so you resort to removing them manually, or if there are too many to clear you just give up. This is where CLEAN comes in handy.

CONCATENATE

Syntax: =CONCATENATE(cell ref/”free text”, cell ref/“free text”,……)

This allows you join the contents of multiple cells into a single one. Let’s say you have a list of names, but the title, first name and surname are all in separate cells but you need to display it all in one.

Use the CONCATENATE function to join them together, referencing each cell reference separated by a comma.

ScreenHunter_55 Feb. 13 12.20

Unfortunately, the function has no idea that these are separate words so there are no spaces between them. To add these in we need to add space characters into our formula.

ScreenHunter_55 Feb. 13 12.21

Any ‘free’ text or spaces that you want to include in your final output must be in double speech marks (i.e. “text or space”).

Having said all of that, my preferred method of joining cell values together is by using & in my formula.

Using &

Which gives you the same end result as CONCATENATE.Your choice.

DOLLAR

Syntax: =DOLLAR(number or cell ref, decimal places)

Not an obvious function and the name can be misleading. Basically it formats a number to your SYSTEM’s currency. So if you are in the UK, somewhat confusingly it formats a number in GBP with a £ sign in front. Let’s say I want to display a comment combined with a cell value….

ScreenHunter_55 Feb. 13 13.22

It displays OK, but it has not brought through the currency formatting from the source cell (B1). To get this to display I need to use the DOLLAR function.

ScreenHunter_55 Feb. 13 13.24

In the example above I have set the number of decimal places to zero. If you don’t put anything in the decimal option, by default it will put in 2 decimal places. If you are working with any other currency and want to display the currency symbol then DOLLAR won’t help you – see below. For that you’ll need to build the currency code into your formula (=”Total Cost €”&B1).

ScreenHunter_55 Feb. 13 16.53

LEFT

Syntax: =LEFT(cell ref, number of characters to select)

This function lets you extract any number of characters (defined by you) starting from the left hand side of the cell. You could apply this to a text string directly in the formula but referencing a cell is the most likely scenario. For instance, I used this to extract the first two letters from a part number as this was the code for the business unit and was useful to extract when pivoting my data.

Left

LEN

Syntax: =LEN(cell ref)

This gives you the length of the contents of a cell. You will rarely use it on its own….the number of characters/numbers in a cell is rarely of interest, unless you are editing content to go out on Twitter and you need to check you have no more than 140 characters! Remember that spaces count as characters.

Len

Note that B4 has returned a length of 10 characters when in fact it should only be 9. That’s because there is a space at the end of excelmate. So that is something you need to check and plan for when using any text function. For that problem, you’ll need TRIM…read on.

TRIM

Syntax: =TRIM(cell ref)

Here’s one that will come in handy more often than you think. This removes any leading or trailing spaces in your cell. It won’t remove spaces between characters!

The ‘excelmate’ bit looks identical but it has removed the end space. If you check LEN again it will now give you the correct number of characters.

Len

RIGHT

Syntax: =RIGHT(cell ref, number of characters to select)

Similar to LEFT, except this time it selects characters from the right hand side of the cell.

Right

Note the odd result for ‘excelmate’. Only three characters are displayed instead of the four required. Remember….spaces are classed as characters too, so in this case there is a space on the end. Once again a good case to use TRIM.

MID

Syntax: =MID(cell ref, position of character to start from, no. of characters to select from start point)

So what do you do if you don’t want characters from either end of the cell contents? This is where MID comes in handy. The function looks in a cell, and you tell it to find a character by its position reading left to right i.e. character 3 would be the 3rd character starting from the left. Then, starting from that character, select a number of characters (e.g. 4). So using ‘excelmate’ as an example…

Mid

In this example I have used text directly in the formula but most likely you will refer to a cell.

In Part 2, I will be looking at the following functions:

LOWER, UPPER, PROPER, FIND, EXACT, SUBSTITUTE, REPT and a handful of other functions that you may need once in a  blue moon as well as a few examples of text function combinations.

Advertisements

Posted on March 6, 2013, in Functions & Formulas and tagged , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: