Using Excel text functions to work with analysis codes – part 1

This is the first part of a two part article that was prompted by a comparatively simple query about concatenating text. As well as dealing with that query, we’ll look at some of the simpler Excel text functions for working with text. Part 2 will follow shortly and, in it, I’ll look at some slightly more advanced functions for dealing with less predictable text entries.

Using text functions to deal with analysis codes

First of all let’s deal with the actual query, which asked how to combine text in two separate cells into a single cell. There are two principal ways to achieve this. Perhaps the simplest is to use the ‘&’ within an Excel formula:

In our example we have typed three items of text in columns A, B and C. In cell D1 we have entered the following formula to combine all three into a single cell:

=A1 & ” ” & B1 & ” ” & C1

Alternatively, there is an Excel function that concatenates text in this way. Unsurprisingly it is the ‘Concatenate’ function.

In the following screen shot we have used ‘Insert, Function’ to enter the required details. Note that in order to include spaces between the items of text, we have included “ “ between each pair of cells. Using the Insert Function screen, you just need to enter a space in the appropriate text box – Excel will add the speech marks for you:

So far so good, now let’s see how we cope with combining numbers and text. If we just want the number without worrying about the number format, then we can use exactly the same formula as for two items of text. Here we have included some ‘literal’ text in the formula together with a number in a cell:

As you can see the format is not ideal:

In the following example we have used the ‘Text’ function to format the number in the cell:

Note that you can also use named ranges. So if we name cell B3 as ‘profit’ we could write the formula as:

=”Profit is ” & TEXT(profit,”£#,##0″)

Note that we have included a space after the ‘is’ and before the “ so that the number does not follow on immediately from the text.

To see the text functions available in Word, select Insert, Function and then choose the ‘Text’ category (note that the examples shown are from Excel XP, other versions’ screens are slightly different):

As you can see there are lots of text functions, we will look at a few in detail, but if you want to explore all of them, just scroll through the list using the down arrow key. As you select each function in turn you will see a brief description of what it does towards the bottom of the screen. For more details, click on the ‘Help on this function’ link:

This group of functions can be used to return specific sets of characters from a text string. As you would imagine, Left is used to return a certain number of characters from the beginning of a text string, Right is used to return characters from the end and Mid to return characters from anywhere within the text string.

As an example, we will look at some nominal ledger codes. We will assume that the first two characters represent the company, the next three the branch, and the last four the type of expense or income:

First of all we will use the Left function to list the first two characters in the company column. We can either use Insert, Function or just type the function in directly if we know the required syntax:

=LEFT(A7,2)

Now let’s use Right in a similar way to sort out the four characters from the end of the code:

=RIGHT(A7,4)

As you can see, the syntax of Left and Right are very similar, just referring to the cell holding the code and the number of characters. The final function that we will look at in this section is ‘Mid’ and the syntax for this one is slightly more involved because we need not only to specify the number of characters, but also from which character to start. For this reason you may find it easier to use Insert, Function:

This should create the following formula:

If we now copy the three formulae down to the end of our list, we can see how our text string has been split into the three different sections:

About these ads

20 responses to “Using Excel text functions to work with analysis codes – part 1

  1. Hi,

    above examples are based on fixed # of digits in e.g. A7. In my case I have the same issue, though the only fixed part is 6 digits on the right.

    e.g:
    John Deere Total
    Martin Bradley Total
    Marilyn Monroe Total

    I want only the names to be returned, not the ” Total” text.

    I hope you can help me out

    Peu

  2. update: I got it:

    =LEFT(A7,FIND(RIGHT(A7,6),A7)-1)

  3. Beat me to it – well done.

    =LEFT(A7,LEN(A7)-6)

    Is slightly simpler – I think LEN() is covered in part 2 of the Excel text functions article.

  4. I never realized that Excel ventured quite this far into the realm of programming languages…

    It’s finding functonality like this that make the price of Office more justified in my eyes.

  5. Question: is it possible to check if a string contains another string, and if it doesn’t, then to return a 0 (or any other known character)?

    All that I found so far are the FIND and SEARCH functions, but they both return #VALUE if the search string is not found. :-(

  6. I think you could do what you are after by using IF() and ISERR() with FIND() or SEARCH() – for example:

    =IF(ISERR(FIND(“test”,K25)),0,”found”)

  7. Thanks a lot!

    I ended up finding that feature later in the night – but it still seems more like a work around.

  8. I really don’t want to waste your time, but I am going to ask one final question.

    Is there any way to “cast” (like in a programing language) one variable as another, if you know for sure that it belongs to the latter type?

    For example, there is a text string. I obtain it with something like MID(A7, 5, 2). And, Iknow that there are always 2 number at that position.

    E.g.: Avac39ado

    And then I would like to compare the obtained number to another number: IF( MID(A7, 5, 2)<40, “yes”, “no”)

    But, the output always seems to be “no”. My guess was that it was being caused by the variables being of different types.

    Because there is a TEXT() function which converts the input to a text field, but I couldn’t find one for integers.

    Thanks again.

  9. Part 2 of this post might help http://kitss.wordpress.com/2007/04/28/using-excel-text-functions-part-2/ it includes the use of the VALUE() function to convert text to a number

  10. It worked!

    Thanks again for helping out an Excel novice.

  11. Pleasure – glad to be of help

  12. Dear all, i wonder who could help me with a difficult formula .

    cel A1 contains = AIR TPT MMC Total
    i want to return in another cell “AIR TPT MMC”
    so withtout the word “Total”.

    What formula can i use?

    brgds
    Sharon

  13. Hi Sharon

    It depends on how consistent the cell contents are, if each cell contained the same number of letters, then you could just use =Left(a1,11) for example. If there were different numbers of characters but there was always the ‘Total’ bit at the end. You could use something like =left(a1,len(a1)-6). Or if Total couldn’t appear anywhere but at the end: =substitute(a1,” Total”,””) – be careful because substitute is case-sensitive.

    I hope one of theses helps

    Kind regards

    Simon

  14. Is it possible to type parts of a formula in one or more cells (without including the = sign), then bring them together for a calculation in another cell?
    Eg. To get a final formula in cell C4 is it possible to join the characters from three other cells together (the three other cells do not start with the = sign so Excel doesn’t know it’s a formula)
    Eg. In cell C1the characters are typed SUMIFS(D1:D9,A1:A9,
    In cell C2 the characters are typed “Mouse”,
    In cell C3 the characters are typed B1:B9,”Clogs”)
    Can these all be joined in cell C4 so that cell C4 reads =SUMIFS(D1:D9,A1:A9,”Mouse”,B1:B9,”Clogs”)

  15. Hi John, the INDIRECT() function (as described in detail here: http://beancountersguide.co.uk/2007/04/28/excel-indirect-function-save-hours/ ) will allow you to construct a cell reference out of text held in other cells, but I don’t think it would let you construct the whole SUMIFS() function in the way you say. You could enter the cell references into cells say:
    C1 D1:D9
    C2 A1:A9
    C3 B1:B9
    C4: mouse
    C5: clogs

    =SUMIFS(INDIRECT(C1),INDIRECT(C2),C4,INDIRECT(C3),C5)

    I haven’t quite worked out why you are trying to construct the SUMIFS() formula in this way – no doubt you have your reasons!

  16. Hello and thanks for your quick reply.

    There were a couple of reasons I wanted to do this.

    One was for a simple addition of numbers within one cell. Eg A simple addition is displayed in Cell A1 and is shown as 1+2+3 (no “=” sign so it is not a calculated formula). I was hoping for a simple function that would then allow me to use those characters in cell A1 (including the mathematical operators), to make the calculation in Cell B1 (as if the only thing needed in cell B1 to make the calculation was the “=” sign).

    I know I could have made the calculation and received a result in B1, by typing in cell B1 =1+2+3. I could have then had Excel automatically display the formula in A1. However, I wanted it to work the other way around. Probably because I’m just awkward.

    The other reason was to try and to cut down on the lengths of formulas on one sheet. The SUMIFS formula is repeated down several rows with only the criteria changing. Your example could cut it down a bit more for me by grouping my sum ranges into another indirect function. I think I’m moving beyond my limited abilities doing that though, as my data ranges already contain an INDIRECT function.

    At the moment I am already using the INDIRECT function to create my data range. Eg to get a data range of Stairs!H8:H60 I am using the following within my SUMIFS formula.

    Stairs!H8:INDIRECT(“Stairs!”&”H”&M2)

    The cell M2 refers to a variable row number and in this example I am using 60.

    I have 3 or 4 data range instances of that, within each SUMIFS formula, except the “H” column reference has other letters for the different criteria ranges.

    Unfortunately I can’t just type this into a cell and have the INDIRECT function use the data to construct the cell reference I need.

    Maybe I’m just going about it all the wrong way.

  17. Hi John – I think I’d need to understand better what you’re trying to achieve to know whether or not you’re going about it the right way. Contact details at http://www.tkb.co.uk if you want to get in touch directly.

  18. Simon,
    I hope this is an easier question. I have concatenated in a cell and am using the TEXT() formula to format the number. The number can be positive or negative dollars. Is there a way to format it to show the negative dollars appropriately?

    Specifically:
    =”Total for 2009 = “&text(A1, “$#,##0.0)

    This only works for positive dollars. When it’s negative it looks like this:
    $-570.1

    I’m hoping it will format like this:
    -$570.1

    Thank you in advance – your knowledge is amazing.

    Best regards,
    Kevin

  19. Hi Kevin – the text format can use the sections of a normal custom number format, so section 2 will define the format of a negative number. The sections are separated by a semi-colon, so I think the following should do what you want:

    =”Total for 2009 = “& TEXT(A1, “$#,##0.0;-$#,##0.0″)

    Kind regards

    Simon

  20. You are awesome. Thank you very much!

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