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:

Advertisements

Automatic continued in Word

Excellent question from one of my clients working on a Word template for their letters, how could the word ‘continued…’ be included automatically on page one of each letter, but only if the letter was longer than a single page?

A more standard question is how to include space for the headers and footers of preprinted letterhead stationery on page 1 only. This can be done by ensuring that in the Layout section of File, Page Setup, the ‘Different first page’ option is selected. This allows you to set up empty headers and footers on page one of the right size to allow for the letterhead but, because of the ‘Different first page’ option, they will not appear on subsequent pages.

The ‘continued…’ question is not so straightforward. The headers and footers for the letterhead are required on page one, whether there is one page or many pages, but as we have said, the ‘continued…’ should only be included when a second page is needed. After a fair amount of thought, the following solution came to mind. Set up the ‘Different first page’ option as before, and this time, in the appropriate position of the header or footer insert a Word ‘IF’ field. The IF should check whether the number of pages in the document is greater than 1 and if so, include ‘continued…’, and if not omit the word.

The Word field should look something like this:

Word IF field code

Be very careful when entering the Word field – forgetting a space or getting the syntax wrong in any other way will probably stop it working properly.

If you are not very familiar with using Word fields, the best method of inserting an IF field is probably to use the Insert, Field option in two stages.

First use Insert, Field to insert the IF field. Don’t worry about calculating the number of pages at this stage, but instead just type in a placeholder – for example the word ‘pages’:

i.e.

IF pages > 1 “continued….” “”

The IF field compares two ‘expressions’ and then prints one item of text if the comparison is evaluated as ‘true’ or a different item of text if it is ‘false’.

In fact, in this case because we don’t need anything printed if the result is false we could omit the ‘FalseText’ altogether:

IF pages > 1 “continued….”

Word IF field

Then right click on the resulting field result, and choose Toggle Field Codes to show the underlying code. Double Click on the word ‘pages’ to select it, then go to Insert, Field again and this time use the field DocProperty, Pages:
Word pages property field

Now select the entire field, right click, and choose Toggle Field Codes again to toggle back to displaying the result of the field.

Finally, to make sure the field result is recalculated every time the document is printed, set the ‘Update field’ option in the Tool, Options, Print screen:

Update fields option

It’s your round

Rounding can often be a problem in a spreadsheet. We’ll start with some relatively simple background stuff then move on to some lesser-known aspects of rounding and displaying rounded values.

What you see isn’t what you calculate – mostly!

The first thing to grasp is that the way Excel displays the contents of a cell generally has no effect on the actual contents of that cell as used in calculations. So a value of 100.05678 will remain as 100.05678 even if the format of the cell is set to display no decimals. There is one important exception to this. There is an option in the ‘Calculations’ tab of the Tools, Options screen that forces Excel to change the contents of the cell to match the ‘precision’ of the format. So if the cell is formatted to display no decimal places, the contents will be permanently rounded to no decimal places – our 100.05678 would become 100.00000:

Before turning on ‘Precision as displayed’

Before precision as displayed

and after:

After precision as displayed

This is an option to be used with great care. It applies to the entire workbook and as soon as it is turned on potentially decreases the accuracy of any figure displayed to fewer decimal places than it actually contains.

Round function

A less drastic method of avoiding rounding errors is to use Excel’s ROUND() function. This allows you to round a number to a specific number of decimal places for use in calculations, without changing the original number. Round takes two arguments – the number to be rounded and the number of decimal places to round to. In this case we have used =ROUND(A2,0) to round to round pounds:

Rounding example

The ‘sum’ formula in cell C5 sums the rounded numbers in C2 to C4, so returns 99 rather than 100. If you need the total to be a particular figure, for example if the 100 was a profit share, then you could set the final rounded figure to be the total minus the sum of the other rounded figures. In this case we have entered a figure of 100 in cell E5, then rounded the calculation of the two shares in E2 andE3, with E4 being =E5-SUM(E2:E3)

Rounding profit shares

More fun with rounding

As well as 0 for no decimal places or a positive number for that number of decimal places, you can also enter a negative number as the decimal place argument of the Round function. For example you could enter -3 to round to thousands:

Rounding to thousands

To change the format to display numbers rounded to thousands you would use the following custom format:

#,###,

for 1,235k

#,###,k

for millions

#,###,,

for 1m

#,###,,”m”

The speech marks are necessary for the m, but not the k, because m is used in formatting to signify a month format.

Peril-sensitive formatting in Excel

Well, given the name of the site I had to do this one sooner rather than later!

Some of you may remember the peril sensitive sunglasses worn by Zaphod Beeblebrox in the Hitchhiker’s guide to the galaxy. Excel has its own version of this useful accessory which it refers to, rather strangely, as ‘Conditional formatting’.

The point of peril sensitive sunglasses was to protect the wearer from danger by turning completely opaque when any threat was imminent. This concept is highly relevant to spreadsheets which often communicate financial or other information that may be upsetting to the user. In this example we are listing the weekly turnover figures for 7 shops, and also including a ‘peril’ level. Results below the peril level will trigger alarm and dismay, so we will use peril sensitive formatting to hide them. Select the cells containing the values – B4 to B11 and then choose Format, Conditional Formatting. Choose ‘Cell Value Is’, ‘less than’ and then refer to our peril level cell, B1. Then click on the Format button and set the ‘Patterns’ to a black background, and the Font to a black font colour:

Peril-sensitive formatting in Excel - black font colour

Once the ‘Pattern’ and the font have been changed to black the screen should look as follows:Peril-sensitive formatting in Excel

If we click the ‘OK’ button, and then click outside our selected area, we can see the effect of the peril sensitive formatting:

Peril-sensitive formatting in Excel - result

The formatting is dynamic, so if we enter new values, or change the peril level, the formatting will change accordingly:

Peril-sensitive formatting in Excel - dynamic

You can set up to three different formats on a cell or range of cells (press the ‘add’ button on the conditional formatting screen to add a second or third format). So you could, for example, use a ‘traffic lights’ system to set the background colour of all cells with values below a ‘minimum’ level to red, those with values above a ‘maximum’ value to green and the ones in between to amber. In Excel 2007 conditional formatting has been greatly enhanced to enable the use of a much wider range of formats and icons to highlight certain values.

Portrait and landscape in one document

A relatively straightforward, but hopefully useful, Word tip. Sometimes you might want to include a page in landscape orientation, in the middle of a normal ‘portrait’ document – for example if you need to include a wide table or schedule of some sort. You can achieve this in Word by creating multiple sections in the document. Each section can have it’s own Page Setup settings. You can do this by using Insert, Break. But it is easier to let Word do the work for you as shown below:

Portrait and landscape - animation

 

 

To do this, position the cursor immediately before the place where the landscape section is to begin and choose File, Page Setup. In the ‘margins’ section, set the orientation to ‘landscape’ and from the ‘Apply to’ list choose ‘This point forward’:

word-portland-1.jpg

If you now look at a multi-page preview of your document you will see that the pages after the current cursor position are now in landscape orientation:

word-portland-2a.jpg

If you have your ‘non-printing’ characters turned on, then you will see the section break that has been automatically inserted:

Portrait and landscape - section break close up

If you need the document to revert back to portrait after the landscape section, you will need to click at the end of the landscape pages and use File, Page Setup to create another portrait section again from ‘this point forward’.

Mapping database address fields

Introduction

This item arose from an engagement letter project I was working on, but should be useful whenever you are trying to translate one set of addresses, or indeed any other, fields to another.

A typical situation would be the need to take the addresses from a database that refers to them as follows:

Address1
Address2
Address3
Town
County
Postcode
Country

and import or link them to our database which has a differently structured set of address fields:

Premises
Town
County
Postcode
Country

Obviously, if our original set of fields has no structure, we then just have to guess which fields are mapped to which other fields. The issue we want to deal with here is condensing the three individual address line fields into the one premises field.

Let’s look at this in stages:

Stage one: concatenation

First of all we will look at a section of an ‘Append’ query that will take our 3 original address1-3 fields and join them together, with commas as separators. The append query will then add this combined address field to the ‘Premises’ field in our current database:

Concatenation

Note that we have included a comma followed by a space between each address line, and used the ‘&’ to join all the bits of the address together.

This is all very well, but of course our premises address will look something like:

Concatenation result

This will make it pretty tricky to generate an address in a letter or on a label.

Stage two: carriage return and line feed

What we really need to do is to put each of the individual fields onto a different line. We can do this by replacing the “, ” bits with the characters that will create the required line feed. These are characters 13 and 10 and we need to enter them using the CHR() function as follows:

Chr(13) & Chr(10)

Here is the complete field:

Separate lines

and the result:

Separate lines result

Stage 3: allow for empty fields

We’re not quite there yet. If any of the address fields are blank, we will end up with blank lines in our new premises field. We need to use the Access IIF() function to check each field and only include it if it has something in it.

In fact it’s worse than that because if there is something in address2 but not in address1, we need to include the contents of address2, but not the carriage return/line feed that precedes it. The same issue occurs if there is something in address3, but nothing in either address1 or address2.

We think the following should do the trick – please let us know if you don’t think it will work properly, or if you are able to come up with a more elegant solution:

Premises: [address1] & IIf([address2]<>””,IIf([address1]<>””,Chr(13) & Chr(10)) & [address2]) & IIf([address3]<>””,IIf([address1] & [address2]<>””,Chr(13) & Chr(10)) & [address3])

You know you’ve been using MS Office too long when….

  • Drafting a report by hand you glance at the paper expecting to see red and green squiggly underlines;
  • Whenever you forget how to program the video you look at the TV screen for a small dog wearing a cape;
  • You try to find the ‘Croissant Warming Wizard’ on your microwave;
  • Whenever you think of a good idea, a yellow light bulb appears by your left ear;
  • Your solution to a ‘bag full’ light on your hoover is to turn if off and then back on again;
  • You talk gibberish and expect the words to be corrected automatically as they leave your mouth;
  • You expect members of your family to have optional appearances and personalities;
  • You don’t wonder why you want to connect your toaster to the Internet;
  • You blame everything – faulty traffic lights, unhelpful shop assistants, running out of petrol – on Microsoft;
  • You put together a half hour long PowerPoint presentation to tell the children where you’re going on holiday next year.