The TEXT Function in Excel: Convert Number to Text (2024)

Excel is all about numbers. However, you’d come across situations where you’d use Excel to store text.

Or where you’d want to convert numbers or dates to text to increase their readability or to bring them to a certain format.

That is where the TEXT function comes to the rescue. Rarely used, but the very handy TEXT function can help you with many situations. 😊

This article will teach you all about how to use the TEXT function in Excel.

Download our free sample workbook here to tag along with the guide that follows.

How to convert numbers to text with the TEXT function

One of the easiest and most convenient methods to convert numbers to TEXT is by using the TEXT function.

It turns numbers into text and changes the way they are displayed.

For example, we have a set of numbers below.

Right now, these are formatted as numbers.

1. To convert them into text, write the TEXT function as follows.

= TEXT (A2, “#,###.00”)

What is that weird formula?

We have created a reference to Cell A2 because we want the value in Cell A2 converted to text.

The second argument tells the format of the converted text. The hashes (#,###) tell that the converted text should have a thousand separators three digits from the right.

The decimal and ending zeros (#,###.00) tell that the converted text should have two decimal points.

And that’s it. There are only two arguments to the TEXT function 😊

2. This converts the value in Cell A2 to text.

3. Excel changes the format of Cell A2 from number to text. Note that this cell is now left-aligned.

4. The format is now also changed from 1234.567 to 1,234.57.

This is in line with the format supplied in the TEXT function above.

5. Drag and drop to convert all numbers in the list to text in line with the supplied format.

Convert dates to text with the TEXT function

Using the TEXT function, you can also convert dates to text.

Here’s how you can do that.

1. Take the date in the image below as an example.

The date is in the format 1/1/2020 and is formatted as a date.

2. We not only want to convert the date into text but also change its format.

3. Let’s say, we want to bring it to the format: January 01, 2020.

4. Write the TEXT function as follows:

=TEXT (F1, “mmmm dd, yyyy”)

What is this formula about?

The first argument is a reference to the cell containing the date.

The second argument specifies the desired date format i.e. ‘mmmm dd, yyyy’.

The ‘mmmm’ format code tells that we need the complete name of the month.

A space character and then ‘dd’ specifies the two digits of the date. Again a space character and ‘yyyy’ specifies the four digits of the year.

5. The TEXT function converts the date into text and adjusts the format as below.

Show leading zeroes with the TEXT function

Leading zeros are the zeros that come to the left of a number. For example, 00005. Type this number in a cell, and Excel would automatically turn it into 5. Excel deletes any leading zeros.

Overall, the approach taken by Excel is a good one when you are working with numbers. That’s because 005 and 5 are the same things.

However, in some situations, you might want to keep the leading zeros. So, you might despise Excel being so over-efficient. 😁

How can you do this in Excel?

1. We have a list of numbers below.

2. All of the numbers have varying lengths. The maximum length is 6 digits.

3. Let’s bring them all to the same length (6 digits) by adding leading zeros.

4. Write the TEXT function as follows:

=TEXT (A2, “000000”)

5. Hit ‘Enter’ to see the number in Cell A2 changed to text as below.

Excel has added leading zeros (to the left) of the number in Cell A1. In the end, the length of the number (including zeros) is no more than six digits.

6. Drag and drop the same to the remaining cells.

Text vs Numbers – what’s the problem?

Why would you want to convert numbers to text in the first place?

There could be many reasons.

1. If you want Excel to display a fraction as it is.

For example, type 1/8 into Excel, and Excel would convert it into a date or some other specific format.

If you want to retain the same in Excel, you can convert it into text using the TEXT function.

= TEXT (1/8, “#/#”)

We have specified the desired format above. The fraction format of 1/8 won’t change in the cell.

2. Another problem often posed by the number format is that it deletes any leading zeros.

If you have a number that begins with zeros and you want to retain the leading zeros, the number format won’t help you.

Excel, by default, deletes any leading zeros from numbers.

For example, if you enter the number 098790 in an Excel sheet, it would be changed to 98790.

Converting it into text using the TEXT function and specifying a “000000” format will retain all six digits of the number.

3. Something you must know about numbers converted to text in Excel. You cannot run calculations on them. Check this.

67 in the image above is converted to text (you can see it’s left-aligned). Whereas 30 is formatted as a number.

Apply the SUM function to it as below.

= SUM (B1:B2)

Here are the results.

Excel only gives back the number. It fails to add 67 (formatted as text) to it.

That’s it – What’s next?

That’s a quick overview of the Excel TEXT function. You can use it to format cells in Excel sheets endlessly.

The TEXT function helps you present data just the way you want – using 1000s separators, decimals, currency symbols, fractions, or any other format.

The best part is – it helps you bring your data into a more readable format which doesn’t necessarily have to be the number or date/time format.

You’d thank Microsoft for introducing this smart function to Excel, particularly when you’ve got large data sets to fix.

Not only the TEXT function but other major functions like the VLOOKUP, SUMIF, and IF functions can prove handy. in such situations.

My 30-minutes free email course is designed to help you master them in no time.

Other resources

If you found the TEXT function useful, we are sure to have much more of your interest.

Check out our other articles on the VALUE function and how to use the CONCATENATE function to combine texts in Excel.