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").
Would you like to learn more?
The '&' symbol 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)
|3||Out Patient visits||08-Jun-07|
|6||Out patient activity: Dr Smith|
|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|
|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")|
|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'.
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 be advised 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 '11/01/2000'.
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
file. It has been rewritten for Excel 2010.
|file: xltextabs.htm||Last updated Dec11||© MeadInKent.co.uk 2013||CMIDX S3 P1 Y|