basic excel: Excel text functions 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.

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: