Excel Functions: Text, Abs and number or date formats

The TEXT() and '&' functions are very useful for including numeric cell values within meaningful sentences which can be understood by a layman. Statements can be constructed which combine words and numeric values (e.g. "The budget for April is £23,000 overspent").

The '&' character is not strictly a function. It is an operator which can be used to join (or concatenate) together two strings or pieces of text. For example: ="The variance is " & D2 where cell D2 contains either "Adverse" or "Favourable". If you join together a piece of text and a number or date the appearance and formatting of the number can be unpredictable unless you use the TEXT function.

The TEXT function converts a number into text and also formats it to the required appearance. It takes the structure: =TEXT(value, "FormatCode"). The complicated aspect of this function is understanding the FormatCodes. Some of them are shown in the following example and tables. Alternatively use Excel help and search for 'Formatting numbers : Custom formats'.

The ABS function changes negative values to positive (i.e. absolute) and takes the form =ABS(value)

- K L M 3 Out Patient visits   08-Jun-07 4   Doctor: Dr Smith 5       6 Out patient activity: Dr Smith     7 Planned activity 100   8 Patient attendances 75   9 Variance -25   10       11 Dr Smith has undertaken 25 less episodes   12 This represents an adverse variance of 25.0%   13 Reporting month: June 2007 www.meadinkent.co.uk

Click to open this PDF document

Note that the equivalent function to TEXT() in Access is called 'Format'.

These format codes give a flavour of how numbers can be displayed as text. If you want to format the appearance of numeric, date or text values in cells and retain their original data type use the ribbon option [Home] Cells | Format | Format Cells | Numbers and make your choice.

Unfortunately many of the default formats within Excel are unsatisfactory and you may wish to create your own custom formats - either see the section on Configuring Excel or use the Custom number category in the format cells dialog box (right).

Dates and times in Excel are stored as numbers (e.g. 36536.799). These numbers sometimes appear unexpectedly when cell formats have been accidentally changed. You may change the cell number formatting to replace the numbers with a more conventional appearance such as '15/01/2010'.

To change a number format to a date, select the required range of cells and then choose [Home] Cells | Format | Format Cells | Number Category: Date.

If a cell value unexpectedly appears as a series of hashes (#) as in cell F2 (see diagram to the right) this is probably due to the column being too narrow to display the value in the selected format. Try widening the column by dragging it to an appropriate size (or use [Home] Cells | Format | Column Width) or reduce the number of decimal places.

Get this information as a document accompanied by Excel worksheets

Click here for details about obtaining this file. It has been rewritten for Excel 2010.