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.
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.
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.
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.
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.
Which gives you the same end result as CONCATENATE.Your choice.
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….
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.
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).
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.
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.
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.
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.
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.
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.
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…
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.