Presentation of the function TEXT
TEXT function converts any number data into a text format according the parameters you give to the function.
TEXT function has two parameters
- the number
- the format to apply
Problem to solve
Let’s take an example with the date. When you have a date in a cell, you can display it in a cell with figures (as 08/28/2011) but also letter (Sunday, August 28, 2011) as it has been seen on this date format page.
On the other hand, if you want to display in the same cell a text with a calculated date – like for instance, “Report of January 1 to January 31” – you can not change the format of the cell with the standard dialog box format number. The following image shows you the result of a cell with text an date, the format of the date is always the format General
It is in this scenario that we need to use the TEXT function to display a date, in any format, within a string.
99% of the time the function TEXT is used with dates.
- First parameter of the function, you have to put your number or your date
- Second parameter, you have to right the format code.
This code has exactly the parameters of the format of dates which are presented in this page
For example, if you want to write the first and the last day of previous month (have a look on these formulas here) and you want to display the date with the month writen with letters, you have to write your formulas as this:
Then you just have to associate (with the symbol &) your text with the calculated date to display in a single cell the expected result. Have a look at his dynamic document, where dates are automatically link to the current date, how to display text and dates in the same cell.