Excel TEXT Function – How to Use

The TEXT function in Excel converts numeric values to a text string. While you'll mostly be fiddling with numbers on an Excel sheet, you may often need to convert a few values into text. The TEXT function is available in all versions of Excel starting from Excel 2003.

Excel TEXT Function

Syntax

The syntax of the TEXT function is as follows:

=TEXT(value, format_text)

Arguments:

'value' – This is a required argument where you input the numeric value you want to convert to a text string. The supplied value can be a number, date, or a cell reference that contains a number, date, or an output from another formula that's a number or date.
'format_text' – This is a required argument where you prescribe a format for the output using a format code. You can use any formatting code that Excel recognizes.

Format Codes for TEXT function

Here is a list of the most commonly used format codes that can be used with the TEXT function:

CodeDescriptionExample
#Placeholder that displays significant digits.#.## – displays up to 2 decimal places. This means if the value fed to the function is 1.755 the result will be 1.76
0Placeholder that displays insignificant zeros.#.000 – always displays 3 decimal places. This means if the value fed to the function is 1.75 the result will be 1.750
?Placeholder that leaves a space for insignificant zeros but doesn't display them. Used for presenting aligned decimals.#.??? – displays a maximum of 3 decimal places and aligns the decimal points in the column.
.Decimal point –
,Thousands separator###,###.## – displays a thousands separator with 2 decimal places. This means if the value fed to the function is 175000, it will display as 175,000.00
dDay of Week or Monthd – one or two-digit number representing the day of the month without a leading zero ranging from 1 to 31

dd – two-digit number representing the day of the month with a leading zero ranging from 01 to 31

ddd – three-letter abbreviation for the day of the week (Mon to Sun)

dddd – full name of the day of the week (Monday to Sunday)

mMonth of the Yearm – one or two-digit number representing the month of the year without a leading zero ranging from 1 to 12

mm – two-digit number representing the month of the year with a leading zero ranging from 01 to 12

mmm – three-letter abbreviation for the month of the year (Jan to Dec)

mmmm – full name of the month of the year (January to December)

yYearyy – two-digit number representing the last two digits of the year (e.g. 08 meaning 2008 or 20 meaning 2020)

yyyy – four-digit number representing the year (e.g. 2008, 2020)

hHourh – one or two-digit number representing the hour component without a leading zero (1 to 24)

hh – two-digit number representing the hour component with a leading zero (01 to 24)

mMinute (when used as part of time)m – one or two-digit number representing the minute component without a leading zero (1 to 60)

mm – two-digit number representing the minute component with a leading zero (01 to 60)

sSeconds – one or two-digit number representing the second component without a leading zero (1 to 60)

ss – two-digit number representing the second component with a leading zero (01 to 60)

AM/PMRepresents the time in 12-hour clock format, followed by "AM" or "PM" –

Apart from the above codes if you want to display specific characters in your output, you can enter them right into the format code and they will be displayed exactly the way they have been entered. Following are the characters you can input:

CharacterDescription
+Plus
Minus
()Left/right parenthesis
{}Curly brackets
<>Less than/greater than
:Colon
^Caret
'Apostrophe
~Tilde
/Forward slash
!Exclamation mark
&Ampersand
=Equal to
Space

Important Characteristics of the TEXT function

  • The TEXT function doesn't convert numbers to words, just text strings. For instance, if you convert 10,000 using the TEXT function, Excel will then recognize it as a text string and not a numeric value, but the function can't convert it to the words "Ten Thousand."
  • You'll need to specify a format for the converted text.
  • If you enter the format code incorrectly, you'll receive a #NAME? error.
  • Once you convert a numeric value using the TEXT function, you can't manipulate it in your workbook calculations.

Examples

Let's try to see some examples of the TEXT function.

Example 1 – Plain Vanilla Formula for the TEXT Function

Let's use the formula on a simple, Excel-recognized date just to look at how the TEXT function works. We'll supply a date to the function and try to format it as a "dd.mm.yyyy" text string. Here's the formula that will help you accomplish this:

=TEXT("12/25/2000","dd.mm.yyy")

Excel-Text-Function-Example-01

Notice how even though the original date is in the mm/dd/yyyy format, you can use the TEXT function to not only convert this date into a text string but also display it in the dd.mm.yyyy format. If you're aiming for a more sophisticated output, you do have the option to make your date look more textual and less numeric. You'd just need to use a different format code, like so:

=TEXT("12/25/2000","mmmm d, yyyy")

Excel-Text-Function-Example-02

This gives you a neater output. You can, of course, obtain a lot of different outputs you offer by using the relevant format code.

Alright, that takes care of the bare-bones formula, let's do some other cool things using the TEXT function

Example 2 – Basic Arithmetic and Concatenation with the TEXT Function

Before we get to the actual example, let's take a moment to see what you're going to learn here.

TEXT function allows us to perform basic arithmetic operations with the values that are supplied to it. Instead of directly supplying a value or cell reference, you can do some arithmetic within the argument.

And in addition to this, it also supports concatenation. Concatenation relates to combining the output of the TEXT function with another text string to make the output more contextual.

Okay, so consider that you're at your job or business desk looking at a price list. You plan on offering discounts to customers, and each product has a different discount rate. You have a list of customers that have gotten in touch with you to claim their discount, and you need to compute their final price. You plan on using the mail merge feature in MS Word and send the customers a letter about their final price.

You've got the original price and the discount rate for Product A listed on your sheet. In the third column labeled MESSAGE, you want the final message that you'll send to your customers. Here's the formula you can use to do this:

="The discounted price is "&TEXT(A2*(1-B2),"$###,###.00")

Excel-Text-Function-Example-03

There are a couple of things going on in this formula. The first portion of the formula is just a plain text string; no formulas in the first portion. The text string is concatenated using the ampersand (&), which means the text string will be connected with the text string that the second portion of the formula returns.

Let's switch over to the second part of the formula, where we've used the TEXT function. The first argument, this time, is an arithmetic operation instead of a numeric value supplied directly or as a cell reference. The calculation is essentially for the discounted price. The second portion of the formula is again just a format code, which will format the output in a way that it has a comma separator after every three digits, and with two decimal values at the end.

The final output gives you a ready to import text string that you can use in your mail merge letters or envelopes for marketing. Who knew Excel could contribute to your marketing efforts, eh?

Alright, let's flex our muscles a little more.

Example 3 – Add Padding Zeros to Numbers with the TEXT Function

This is one of the most common use-cases of the TEXT function. When you have a list of numbers with a varying number of digits, and you want a nice list where all numbers that have less than a certain number of digits have 0s at the beginning. Let's make this a little clearer with an example.

Say you've got some numbers piled up in your worksheet. The smallest number is 2 digits, while the largest number is 7 digits. You want to give it a tidy look so all numbers will essentially be 7 digits. Here's the formula you could use:

=TEXT(A2,"0000000")

Excel-Text-Function-Example-04

You're effectively just giving the cells a format where any number with less than 7 digits will have 0s as a prefix for padding. Note that once you've applied the TEXT function to these numbers, they're no longer numbers that you can manipulate on your spreadsheet, they're just text strings.

You can now feed your secret OCD just right.

Example 4 – Get Day Name from Week

You've been told that some employees have been slacking off and you intend to set things straight with them. You have a software that records attendance from employees, but unfortunately, the software also includes the weekends into the list of days when employees wouldn't have punched their cards in.

You now have a list of dates on which your employees didn't show up, but you need to weed out the holidays from working days. Manually, this could take you about a million years. Thanks to Excel, we've got a way to accomplish this within a couple of seconds.

Assume that you give Saturdays and Sundays off. Then, you'll need to identify the dates that fall on a Saturday or Sunday so you don't end up nagging the employee for no good reason. Once you've got the Saturdays and Sundays out of the list, you know which workdays your employees didn't show up on.

Perhaps, you could call upon the TEXT function for some help with the following formula:

=TEXT(A2,"dddd")

Excel-Text-Function-Example-05

Since the format has been set to "dddd" it shows the full name of the day. You could also choose to format the output as "ddd" to display the output as Mon, Tue, Wed, etc.

As you'd have noticed, all we're doing with this formula is just changing the dates into text and formatting it such that the final output displays the day component of the date. If you have a fairly large list, you can also give your output some conditional formatting so you can eyeball employees that have taken excessive holidays.

There's one more thing you can use the same formula for, but you'll need to tweak it just a tad. Let's talk about that in the next example.

Recommended Reading: Get Day of Week From Date In Excel

Example 5 – Get Month Name from Week

Say you're now keen on analyzing which month witnessed the highest leaves. Maybe, the absenteeism was a cause of flu season. To understand this, you need to see which month the dates where employees didn't show up fall in.

To accomplish this, you can use a slight variation of the previous formula. If you hadn't guessed it already, the formula is:

=TEXT(A2,"mmmm")

Excel-Text-Function-Example-06

This is the same formula we used in the previous example, but this time, the final output has been formatted to show the name of the month instead of the day. Note that all output cells are now formatted as text strings and you cannot use them for manipulation on your worksheet.

Like we discussed in the previous example, you can also give the output in this example different formatting if you so choose. For instance, if you want to display the three-word abbreviation of a month (like Jan, Feb, Mar, etc.) instead of the full name, you could use the "mmm" format.

All set? Alright, time to play with some numbers.

Example 6 – Convert Numbers to Phone Number Format

Have you had to skim through what looks like a gazillion phone numbers, all of which have a country code as a prefix? It makes you dizzy looking through the digit-dense list, but a little formatting could make things a lot easier for you.

Say you've got some cell phone numbers listed on your spreadsheet. They're all 10-digit numbers but have a country code in front of them. You want to separate the country code so it's easier to see which country the number belongs to.

Remember, the TEXT function gives you a ton of ammunition for custom formatting the output. Therefore, it can be a great tool to tidy up your spreadsheet regardless of what data you have in your cells. Here's the formula we'll use to clean up the list of phone numbers:

=TEXT(A2,"(##) ##### #####")

Excel-Text-Function-Example-07

You now have all country codes in brackets, well organized, and easy to read.

Also, remember those characters we said you could add to the format and they'll appear as they are entered in the formula? We've used parentheses in this formula and notice how they appear as-is in the final output. If you prefer, you could even add dashes instead of spaces between the numbers, and it would work just fine.

Pretty cool, huh?

Example 7 – Concatenate a Date with Text Using the TEXT Function

Say you want to create an output where there's a text string concatenated with the DATE function to display something like, "Today is January 1, 2001". Go ahead and give it a try. If you don't know the formula, here you go:

= "Today is " &DATE(2001,1,1)

See the problem?

This formula gives you an output where the DATE is displayed as a serial number. Technically, Excel is doing everything that it's supposed to. For Excel, a date is a serial number and is displayed as such unless otherwise formatted.

As we discussed previously, the TEXT function carries plenty of power when it comes to formatting the output. So, we'll add the TEXT function to the mix just to change the format of the output.

Note that the Excel function is doing nothing except for formatting in the following formula:

="Today is "&TEXT(DATE(2000,12,25),"mmmm d, yyyy")

Excel-Text-Function-Example-08

Notice that all that's different in this formula is that we've nested the DATE function inside the TEXT function so we can format the serial number as "mmmm d, yyyy". The DATE function relays the serial number to the TEXT function, which then converts it into the prescribed format.

Easy-peasy, isn't it?

Alright, that's a wrap for the TEXT function. TEXT function, quite literally, is one of the most versatile functions when it comes to formatting. It can help you organize and tidy up almost any data set you have based on your preferred output format. So, once you've championed all these formulas (they're quite easy, aren't they?), we'll have the next tutorial ready for you to get your hands dirty with. Meanwhile, stay curious!

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.