MeadInKent
| Feedback | Configuring Excel | Displaying numbers as words |
excel

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
  save this free reference page with examples of 8 Excel functions

Click to open this PDF document


Formulas used in the example
cell M3 = NOW()
cell M4 text entered by user
cell K6 ="Out Patient Activity: " & M4
cell K11 =M4 & " has undertaken " & TEXT(ABS(L9),"0") & IF(L9<0," less", " more") & " episodes"
cell K12 ="This represents " & IF(L9<0," an adverse", " a favourable") & " variance of " & TEXT(ABS(L9/L7),"0.0%")
cell K13 ="Reporting month: " & TEXT(M3,"mmmm yyyy")

Examples of Format Codes
- Q R S T U
19 no format 1234.56   todays date 08/06/07
20 =TEXT(R19, "#, ##0.0") 1234.6   date without formatting 39241.32
21 =TEXT(R19, "#, .0") 1.2   =TEXT(U19, "d/m/yyyy") 8/6/2007
22 =TEXT(R19, "0") 1235   =TEXT(U19, "ddd d mmm") Fri 8 Jun
23       =TEXT(U19, "dddd dd") Friday 08

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

The Excel Format cells dialog box

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.


excel Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file: xltextabs.htm Last updated Feb14 MeadInKent.co.uk 2016 CMIDX S3 P1 Y