Monthly Archives: March 2013

Excel: Text Functions (Part 2)

Continuing from my last blog, here are the remaining text functions I have found useful:

LOWER

Syntax: =LOWER(text or cell ref)

This will convert any characters in UPPER case to LOWER case. Your immediate thought will probably be ‘when am I ever going to need that?’, but people enter data into your spreadsheets all in lower case or upper case or some weird illogical combination of both, and so to give your text values some sort of uniform appearance, this sort of function comes in handy. You will most probably use this in combination with other text functions.

Lower

UPPER

Syntax: =UPPER(text or cell ref)

This gives you the opposite to the previous function, converting all LOWER case characters to UPPER case. As with LOWER, you will probably use it in combinations with other functions.

Upper

PROPER

Syntax: =PROPER(cell ref)

A function of limited use really, and probably limited to using with names. This one capitalises the first letter of every word in a cell. This can be useful when working with names that have been entered in some haphazard way…

Proper

…but avoid using with sentences or blocks of text.

Proper_text

Not pretty!

FIND

Syntax: =FIND(what to find, cell ref)

Another text function which on its own is of little interest but works best when combined with other functions. Basically it gives you the position (reading from left to right) of whatever it is you are trying to find. If you try finding a text string (i.e. multiple characters or even a word) it will give you the position of the first character or digit of what you are trying to find.

Find

In column B, I am looking for the dash (-) and in column C, looking for the text string “Sm”. Not really of much use until combined with other functions to be honest but check out the combinations section further on for some examples. Also note that it will only give you the position of the first instance it finds starting from the left.

EXACT

Syntax: =EXACT(cell ref 1, cell ref 2)

Basically, this tells you whether contents of two cells are identical or not. Another one that is best used in combination with other functions, testing values before they are manipulated in some way.

Exact

In column B, I am looking for the dash (-) and in column C, looking for the text string “Sm”. Not really of much use until combined with other functions to be honest but check out the combinations section further on for some examples. Also note that it will only give you the position of the first instance it finds starting from the left.

SUBSTITUTE

Syntax: =SUBSTITUTE(cell ref, old text, new text [,optional instance number]))

Whereas REPLACE replaces everything that meets your criteria, SUBSTITUTE gives you the choice to (effectively) replace every instance of your criteria or a specific instance i.e. the 2nd or 3rd time etc. that it appears in the cell. Let’s say our part number has been changed where the 2nd dash needs to be substituted with a forward slash. This function, has quite a specific use and may not be one you will use on daily basis, but it is worth knowing and as with most functions works best in combination with other functions.

Substitute

REPT

Syntax: =REPT(“character/letter/symbol”, number of times to be repeated)

In terms of functions, this one ranks amongst one of the most pointless….but it can come in very handy to create a fake chart! Basically you pick a character and work out how many times you want to repeat it in a cell.

Rept

Sparklines in 2010 have made this a tad redundant but can still be quite effective for a quick chart effect, and you can get creative using Wingdings or Webdings as well as apply conditional formatting! Awesome!

And then it’s all about putting them together in various combinations to extract and modify text…

Other functions in the TEXT category that may be of interest but personally have either never used or used just once for a very specific job are;

REPLACE – replaces characters but you are more likely to use CTRL + H and use the replace dialog box.

CHAR and CODE if you need to generate random characters based on their code, or to work out the code for a given character e.g. A= 65, a = 97……85 = U.

SEARCH returns the position of a specific character (reading from left to right) within a text string specifying from which point you want to start searching.

TEXT allows you to format a value into a specific text format which of course means you can no longer perform calculations on that value.

VALUE is TEXT’s counterpart converting numbers formatted as text into numerical values.

So that’s TEXT functions in Excel, but as I have mentioned a few times, the power of these functions really comes through when you combine them together. Here are a few examples, but how many and how you combine them will depend entirely on your own requirements…

  1. =UPPER(LEFT(TRIM(A2),3))Removes any leading/trailing spaces, extracts the first 3 characters starting from the left and then converts them into upper case.
  2. =LEFT(A2,FIND(“ ”,A2)-1)Works out the position of the first space in the cell, takes away 1 from that value to give you the position of the last character before the space and extracts all characters up to that point.

    Left

  3. =IF(LEFT(A4,1)=”T”,A4,E4&”.”&D4&”@”&F4)If the first letter is a “T” then display that value, otherwise join a number of cells together adding a dot and an @ sign to make up an e-mail address.
  4. =SUBSTITUTE(IF(ISERROR(LEFT(A2,FIND(“@”,A2)-1)),A2,LEFT(A2,FIND(“@”,A2)-1)),”.”,” “)This checks the cell contents to see if there is an @ symbol. If not (in this example…if the test produces an error) display the contents, but if no error is generated take all characters to the left of the @, and then replace any dots with spaces.

ScreenHunter_57 Mar. 04 20.15

So now it is just a case of experimenting with different combinations. If you are not confident nesting functions then build them up a bit at a time. Let’s take the first combination example:

=UPPER(LEFT(TRIM(A2),3))

Think about the problem logically to apply the functions in the correct order:

  • Remove any spaces that may or may not be there so we TRIM first as we don’t want to start counting characters before we remove any chances of counting spaces or non-printable characters.
  • Then what do we want to extract? So now we can apply the LEFT function to the output of the TRIM function.
  • Finally, we want to convert whatever comes out of that into UPPER case.

So apply one function at a time, make sure it works at each stage with each additional function. If you try to do it all in one go, chances are you’ll only confuse yourself and end up ‘correcting’ things that are fine and making things worse. With experience you will be able to string these together in next to no time.


 

Advertisements

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.