TEXT Function in Excel

=TEXT(value, format_text)
↑Click any parameter above to learn what it means — it'll highlight in the live example below.
  • value: (What to format) The number, date, or cell reference that you want to convert into text. Required.
  • format_text: (How to format it) The format you want Excel to use, such as a date, currency, or percentage. This must always be inside quotation marks.  It can be a date format ("mm/dd/yyyy").  It can be a currency format ("$0.00").  Or it can be a custom number format ("00000"). Required.

What is the TEXT function in Excel?

The Excel TEXT function converts a numerical value into a text string and applies a specific display format (like a date, currency, or percentage) to it.

TextIntermediateWorks in Excel All versions
!Golden Rules

TEXT's 2 golden rules

Reading these is a must. Skipping them is the #1 reason TEXT formulas break.

Excel needs the formatting rule to be written as text.
It must be written as =TEXT(A1, "mm/dd/yyyy").
Because the function converts your data into a pure text string, Excel no longer treats it as a number.
If you use TEXT to format cell A1 as $50, and then try to multiply it by 2, you will likely get a #VALUE! error.

Learn with Example: TEXT

  1. Step 1: Understand the Goal We need to build a sentence that says "Total Due is $500.50" for Invoice #101 first, and then automatically calculate it for the remaining invoices.
  2. Step 2: Type Your Sentence Text To start building the summary sentence, click the empty cell next to Invoice #101. Type your desired text inside quotation marks, followed by an ampersand (&) to glue it to the upcoming number. Type: ="Total Due is "&
  3. Step 3: Start the TEXT Formula Now, to ensure the number we are gluing on keeps its currency format, start the TEXT function. Type: ="Total Due is "&TEXT(
  4. Step 4: Select the value For your value, select the raw number for Invoice #101 (B3) that you want to format. Then, type a comma. Type: ="Total Due is "&TEXT(B3, value The raw number or cell that needs to be formatted.
  5. Step 5: Set the format_text For your format_text, type the exact currency format you want inside quotation marks. Close the parentheses. Type: ="Total Due is "&TEXT(B3, "$#,##0.00") format_text The specific visual format you want to apply, wrapped in quotes. Hint: Check out our Excel Custom Number Formats guide for a master list of all format codes.
  6. Step 6: Check the First Result Press Enter. You will now see the raw number perfectly formatted as currency inside your sentence! Result Total Due is $500.50
  7. Step 7: Copy Down and Check the Final Result Double-click the small green square in the bottom right corner of your cell to calculate the summaries for the rest of the invoices instantly! Result Total Due is $500.50 Total Due is $1,250.00 Total Due is $75.25 Total Due is $1,000.00

You need to automatically write a summary sentence for your invoices. How do you combine the total amount with text without losing the currency format (like "Total Due is $500.50")?

C3
ƒx
=TEXT(value,format_text)
ABC
1Invoice Summary
2InvoiceTotal DueSummary Sentence
3#101500.5
4#1021250
5#10375.25
6#1041000
7
8
9
10
Ready
Step-by-step guide1 / 7
Step 1: Understand the Goal

We need to build a sentence that says "Total Due is $500.50" for Invoice #101 first, and then automatically calculate it for the remaining invoices.

When to use TEXT

Here are the most common situations where the TEXT formula is incredibly useful:

  • Combining numbers with text: If you need to join a date or currency amount into a sentence without losing its formatting (such as writing "Total Sales: $500.00").
  • Converting parts of a date: If you have a full date and only want to show the name of the month or the day of the week (such as turning 1/1/2026 into "Thursday").
  • Forcing leading zeros: If you have zip codes or ID numbers that start with zero and Excel keeps deleting them, TEXT can lock those zeros in place.

When not to use TEXT

The TEXT formula is powerful, but there are a few scenarios where you should avoid using it:

  • When you still need to calculate: Once a number is converted by the TEXT function, it becomes a text string. You cannot easily add or multiply it anymore. Instead, use Excel's visual Format Cells menu.
  • When you just want to change the font or color: The TEXT function only changes the characters themselves. It cannot make text bold, red, or change the font size.

TEXT function vs Format Cells menu

Understanding the difference between the TEXT formula and the standard formatting menu is critical.

FeatureTEXT FunctionFormat Cells Menu
Best used forCombining formatted numbers into sentencesChanging how numbers look without changing the actual number
Changes the actual data typeYes (turns numbers into text strings)No (numbers stay numbers)
Can be combined with other textYesNo
Can be used in maths formulas laterNoYes
Excel versionAll versionsAll versions
Ease of useIntermediateBeginner

Bottom line: Use the Format Cells menu for visual styling so you can still use the numbers in calculations. Only use the TEXT function when you are permanently turning a number into a text label.

Common Formats

The top 5 most useful format codes for changing how your numbers and dates look. (These are the exact same universal rules used in Excel's Format Cells menu.)

FormatWith FormulaExplanation
Full Date=TEXT(A1, "mmmm d, yyyy")Turns a date into text like "January 1, 2026".
Short Date=TEXT(A1, "mm/dd/yyyy")Turns a date into text like "01/01/2026".
Currency=TEXT(A1, "$#,##0.00")Adds a dollar sign, commas, and exactly two decimal places.
Percentage=TEXT(A1, "0%")Converts a decimal number into a whole percentage, like "25%".
Leading Zeros=TEXT(A1, "00000")Forces the number to have five digits, adding zeros to the front if needed (useful for zip codes).

For the complete master list of every formatting rule (including times, fractions, and phone numbers), see our full Excel Custom Number Formats guide.

Common Mistakes When Using TEXT

Getting the #NAME? error because you forgot quotation marks around the format_text
This usually happens when you type the format_text rule without putting it inside quotes. For example, =TEXT(A1, yyyy) will fail. You must add quotes around the format rule: =TEXT(A1, "yyyy").
Getting the wrong date because of regional settings
This usually happens when you use a format_text rule that does not match your computer's regional language settings. For example, in some European countries, the rule for year is "a" or "j" instead of "y".
For a complete list of formula errors (like #VALUE! or #NAME?) and how to fix them, see our Common Excel Errors Troubleshooting guide.

Frequently Asked Questions about TEXT

Quick answers about TEXT

How do I format a number as a phone number?

You can use hashes and dashes in your format_text rule. For a standard US phone number, use the rule "###-###-####".

How do I get the full name of a month or day?

Use four letters in your format_text rule. Use "mmmm" to get the full month name (like "January"), and "dddd" to get the full day name (like "Thursday").

Does this work in Google Sheets?

Yes. The TEXT function exists in Google Sheets and uses the exact same syntax and formatting rules as Excel.

How do I change my formatted text back into a normal number?

Because the TEXT function changes your number into a text string, you cannot easily use it in calculations. To change it back into a usable number, you can put it inside the VALUE function (for example, =VALUE(A1)).

Why does my date change into a random 5-digit number (like 45123) when I combine it with text?

Excel secretly counts dates as regular numbers starting from the year 1900. When you try to combine a date with words, Excel accidentally shows you that secret background number. You must use the TEXT function to lock the date into a readable format so the background number stays hidden.

Can the TEXT function make negative numbers red?

No. The TEXT function can only change the actual characters (like adding a minus sign). It cannot change the font, size, or color of the text. To change colors, you must use Excel's visual Format Cells menu or Conditional Formatting.

TEXT Practice Workbook

Download ready-to-use Excel files and practice TEXT hands-on

Complete Practice File
TEXT Practice Workbook
.xlsx · 75 KB
This file contains:
  • All 5 worked examples (finance, sales, HR, ops, data), each with a table to fill in
  • Blank result cells to write each formula yourself: currency, month, percentage, leading zeros, and day-of-week formats
  • The copy-down examples set up so you can practise filling the formula down
  • An answer key to check every formatted result
Download Complete File
Reviewed by Grapte teamLast updated September 2026Tested in Microsoft Excel 365