The TEXT function allows you to show a number (or date, or some other value) as text. But why would you want to do that? Well, sometimes you want to join numbers with text and that only works if both of them are text, so we change the number to text to join it.
In the example above, you can see that the TEXT function is being used to turn a date into the word for the day, and then joined with “Today is “.
The second example, is joining the number for the value of the quote with the words, “The quote is “.
You can see the general layout of the TEXT function:
TEXT(Value, format). Both the value and the format are required inputs. The format needs to be specified in double quotes.
Text is usually used in combination with a formula to combine text. I have used the & symbol to do that, but you can also use concat, which you can read about here.
The way to input the format of the text can get a bit confusing, especially if you want to specify different things for negative numbers or zero.
But, dates and how you want dollars to show are not too bad.
Something like “dd/mm/yyyy” is easy enough to understand what the date will display like.
$#,###.00 means, show the thousand separator. You don’t need to put #,###,### if the number is going to be that big. The #,### means put the thousand separator between every set of three digits. The double zero on the end means that it will always show two decimal places, whether the original number has them or not.
Just remember that the TEXT function is only really needed when you are combining the value with text in a cell. If you just want to display the number by itself in a cell, then you can do all the same formatting in the Number section of the Home menu.