The TEXT function lets us convert numerical values into a text string, and apply various formatting styles to the string.
value – The value which we want to convert to text and apply formatting to it
format_text – The format that we’d like to apply
There are multiple use cases where we can benefit from using a TEXT function:
We can use the TEXT function to extract and format date/time values as we wish.
Let’s see an example of how the TEXT function can let us extract the names of the day and month; and the last two digits of the year:
Use combinations of d/m/y to get the desired format.
We can also use the TEXT function for changing time formats, let’s say – converting from 24H format to AM/PM format;
Use combinations of h/m, combined with AM/PM to get the desired format.
Add leading zero to a number
A very common issue with Excel is when a user is trying to write a number (let’s say – a phone number) that starts with 0. Because Excel treats the entered value as a number, it omits the leading zero – as there’s no meaning to a leading zero if you are working with a number.
One way to preserve the leading zero is by using the TEXT function.
Let’s assume we have an ID number that is 8 characters long and starts with a 0 – For example: 01234567
We can use the TEXT function and apply the following formatting: “00000000” (8 zeroes). If there’s already a digit in place – the digit will be shown. If not – the zero will be shown. As all cells have only 7 digits, we will get a result 1 zero & the 7 original digits:
Or… Just convert the number to a text string…
There are some cases where we will just want to convert data that is stored as a number, into a text string, without any special formatting (For example – If we need to perform a VLOOKUP against numbers that are stored as text).
One of the ways to systematically convert numbers into a text string is by using the TEXT function, with the “0” format:
Tip: We can check if a cell is a text or a number by using the ISTEXT/ISNUMBER formulas 🙂
Practice TEXT function
Let’s practice this function: