The Excel TEXT function is used to convert a numeric value to text in a specified number format. This function is really useful when concatenating strings that will contain a numeric value.
Excel TEXT Function Anatomy
The TEXT function in Excel requires two arguments – the text that needs formatting, and the format you want to use.
=TEXT(value, format_text)
It is very useful for displaying numbers that are used in text strings still in their number format. Examples of this include when using summary text on reports or in chart labels such as “Total sales this quarter: £4,073”.
Convert Text Date to a Date Format
In the example below, the following formula has been entered into cell C2. The date loses its format when combined in a text string. This is now useless.
=A2&" - "&B2
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.
The following formula formats the date in a dd/mm/yyyy format.
=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 |
| ddmmyyyy | 12041974 |
Convert Text Number to a Currency Format
The Excel 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")
When applying number formatting the # symbol displays a number only if necessary, and a 0 forces the display of a number.
The TEXT function is very versatile and there are many examples for its use.
Leave a Reply