TEXT Function Examples and Description
The TEXT function is used to convert a numeric value into text in a specified number format. This function is really useful when concatenating strings that will contain a numeric value.
Take the example below, where the following formula has been entered into cell C2. The date formula loses its format when concatenated. This is now useless.
=A2&" - "&B2
Format a Number within a Text String
By using the TEXT function the date can be concatenated, and an appropriate date format applied.
The number format must be entered within the double quote marks. If these are omitted, the #NAME? error is displayed.
=A2&" - "&TEXT(B2,"dd/mm/yyyy")
You can specify the date format you want by using the same formatting characters that you get when applying custom formatting in Excel.
The examples below show some alternative representations of the date.
|dd mmmm yyyy||12 April 1974|
|dddd dd mmm yyyy||Friday 12 Apr 1974|
TEXT Function Example 2
Display a Currency Format in a Text String
The TEXT function can be useful for displaying summary text that you see on reports and dashboards in Excel. For example, “Total spend for the current month is £2,930.89”.
The formula below calculates the net income value, converts it to text and applies a currency format.
="Net income for this month is "&TEXT(G2-H2,"£#,###.00")