Explanation

The TEXT function lets us convert numerical values into a text string, and apply various formatting styles to the string. 

Syntax

=TEXT(value,format_text)

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

Examples

There are multiple use cases where we can benefit from using a TEXT function:

Date/Time formatting

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 🙂

There are tons of cool uses for the TEXT function. More examples of TEXT function can be found here, here , and here.

Practice TEXT function

Let’s practice this function: