Presentation of the TEXT function

The TEXT function helps you to keep any number format inside a string.

The TEXT function has two parameters

• the number or the date value
• the format to apply

=TEXT(Number, Format)

Problem to solve

Let's take an example with the date.

When you have a date in a cell, you can display with different format as it has been seen on this date format page.

But, if you want to link a text with a value with the symbol &, the date format is not kept 😱😱😱

In this situation, the only way to keep the date format, is to use the function TEXT.

Writing of the TEXT function

1. Keep your extra text between double-quote
2. Add the symbol &
3. Write the formula TEXT
4. The first argument of the function is the value you want to keep

="From "&TEXT(D1,

The second argument is the format you want to return. The values of the code are exactly the same as the codes of the date format

For instance, you can write this formula

="From "&TEXT(D1,"dd/mm/yyyy")

Or this one

="From "&TEXT(D1,"ddd, dd mmm yyyy")

Example for the title of a dashboard

We have seen in this article how to returns the first and the last date of a month.

To write in the same cell the first and the last day you have to write the following formula.

="Report from "&TEXT(DATE(YEAR($B$1),MONTH($B$1)-1,1),"dd mmmm")&" to "&TEXT(DATE(YEAR($B$1),MONTH($B$1),1)-1,"dd mmmm")

The formula is a little bit long but not complex. You have to write 2 times the TEXT function for the first and the last date.