The TEXT Function Explained:
Convert A Number To Text

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

In this tutorial, you learn exactly how to use the ‘TEXT’ function in Excel.

You use ‘TEXT’ to convert a numeric value to text in a specific number format.

For instance, you learn how to convert a date’s number value to text.

Let’s get into it 🙂

As always, the best way to learn how these functions work is by looking at some examples. Let’s get right into it!

Kasper Langmann, Co-founder of Spreadsheeto

*All our examples in this tutorial will be performed in Microsoft Excel 2016 for Windows.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

How to use the ‘TEXT’ function

What is the ‘TEXT’ function?

Like the ‘VALUE’ function, if we type ‘TEXT’ into a cell, the tooltip tells us that it:

“Converts a value to text in a specific number format”

TEXT formula

The syntax for the ‘TEXT’ function is:

‘=TEXT(value, format_text)’

Note the function has two arguments – and both are required.

TEXT syntax

The ‘value’ argument is the value that we want to convert – or the cell reference that contains that value.

The ‘format_text’ argument is the format we want the function to output the new text our function will generate.

The ‘format_text’ argument provides us a lot of options. First, let’s consider a situation where we have today’s date in a cell. For simplicity’s sake, we just type in the function ‘=TODAY()’.

Kasper Langmann, Co-founder of Spreadsheeto
TODAY function

Now let’s say we want to use this value and plug it into the text string “Today’s date is…”.

To do this, we can type the following into cell ‘A3’ to see what happens:

Example of TODAY function

What we actually get is not what we are really looking for.

Our date value in ‘A2’ actually appears in its number form in our newly created string in ‘A3’.

The value that appears when we attempt this, ‘42594’, is the numeric equivalent of the date value in ‘A2”.

This is not what we want so we need to put the ‘TEXT’ function to work.

We want the date to show in the exact same form as it appears in ‘A2’.

We need to state this in our ‘format_text’ argument by placing ‘mm/dd/yyyy’ in double quotes.

mm/dd/yyyy

We can make this even better by choosing a different format.

Let’s take the original date value and convert it to long date form. That way it fits better into our sentence.

This time we do the same thing we did with our previous ‘TEXT’ function.

This time we will use “mmmm dd, yyyy” as our ‘format_text’ argument.

TEXT example

Note that ‘mmmm’ month format is the full month name as compared to ‘mmm’ for the three letter abbreviation of the same.

Now let’s insert our results from ‘B7’ into our sentence text string:

Today's date example

Another example where
TEXT’ proves useful

Here’s another great example of how powerful the ‘TEXT’ function is.

Let’s take a look at some data in a format that really isn’t useful in its raw form.

Say we have data for a particular date and time in number format like the following:

Raw data

At first glance, it seems like we are looking at three date and time values in number form that are exactly the same.

Let’s try to extend these values to two decimal places.

Now we can see that they are not exactly the same – but different times of the same day.

Different times of the day raw data

What we then want to do is get just the time from this value.

We will use the ‘TEXT’ function to do this.

Here we’ll use “h:mm AM/PM” as our ‘format_text’ argument, so we get the time only from this number.

Conversion to time format

This was another quick example of how practical the ‘TEXT’ function is.

It gets the exact data from a number value – all from using one simple function.

The examples where we manipulate dates in number format highlights a common use for ‘TEXT’. ‘TEXT’ also comes in very handy when preparing data for other operations like pivot tables.

Kasper Langmann, Co-founder of Spreadsheeto

Conclusion: Wrapping up…

Now you’ve learned exactly how TEXT works – and its use cases 🙂

TEXT’s use is relatively specialized compared to many other functions in Excel (like IF). But when you need it, it sure is one function worth keeping in your bag of tricks!

Kasper Langmann, Co-founder of Spreadsheeto
2019-03-28T15:25:37+00:00