TEXT function - Microsoft Support (2023)

DieTEXTWith the function you can change the representation of a number by applying the formatformat codes. This is useful in situations where you want to display numbers in a more readable format, or want to combine numbers with text or symbols.

Use:The TEXT function converts numbers to text, which can be difficult to reference in later calculations. It's better to keep the original value in one cell and use the TEXT function in another cell. So, when you need to create other formulas, always consult the original value and not the result of the TEXT function.


TEXT(Wert, format_text)

DieTEXTThe function syntax has the following arguments:

argument name



A numeric value to convert to text.


A text string that defines the format you want to apply to the specified value.


In its simplest form, the TEXT function says:

  • =TEXT(value you want to format, "format code you want to apply")

Here are some popular examples that you can copy directly into Excel to try out for yourself. Note the format codes in quotes.




Currency with thousands separator and 2 decimal places, p. B. $1,234.57. Note that Excel rounds the value to 2 decimal places.


Today's date in MM/DD/YY format, eg. B. 03/14/12


Today's day of the week, like Monday.


Current time, eg. eg 13:29

=TEXT(0,285,"0,0 %")

Percentage, such as 28.5%

=TEXT(4.34,"# ?/?")

Fraction, like 4 1/3

=TRIM(TEXT(0,34,"# ?/?"))

Fraction, such as 1/3. Note that the TRIM function is used to remove leading space with a decimal value.


Scientific notation, such as 1.22E+07

=TEXT(1234567898,"[<=9999999]###-####;(###) ###-####")

Special (phone) such as (123) 456-7898


Add leading zeros (0), e.g. 0001234

=TEXT(123456,"##0° 00' 00''")

Custom - Latitude/Longitude

Use:While you can use the TEXT function to change the format, it's not the only way. You can change the format without a formula by pressingCTRL+1(oTEXT function - Microsoft Support (1)+1on Mac), then select the desired format from thecell format>numberDialogue.

Download our examples

You can download a sample workbook with all the examples of TEXT functions found in this article, plus a few extras. You can add or create your own TEXT function formatting codes.

Download examples of Excel TEXT functions

Other format codes available

You can use the...cell formatDialog box to find the other available format codes:

  1. PressCtrl+1(TEXT function - Microsoft Support (2)+1on Mac) to open thecell formatDialogue.

  2. Select the format you wantnumberEyelash.

  3. ChooseFantasiaPossibility,

  4. The desired format code is now displayed on theuntilCash register. In this case, select alluntilField except semicolon (;) and @ symbol. In the example below, we selected and copied only MM/DD/YY.

  5. PressStrg+Cto copy the format code, then pressCanceldismissed themcell formatDialogue.

  6. Now all you have to do is pressControl+Vto insert format code into your TEXT formula, like: =TEXT(B2,"mm/hh/aa"). Be sure to enclose the format code in double quotes ("format code"), otherwise Excel will throw an error message.

TEXT function - Microsoft Support (3)

Format codes by category

Below are some examples of how you can use different number formats to apply to your values.cell formatdialog box and then use theFantasiapossibility to copy themformat codesfor youTEXTOccupation.

Choose a number format 
  • Choose a number format
  • Leading zeros (0)
  • Show a thousand separator
  • Numerical, monetary and accounting formats
  • Finished
  • Mal
  • Percent
  • fractions
  • Cientific notation
  • special formats

Why is Excel stripping my leading zeros?

Excel is trained to look for numbers entered into cells, not numbers that look like text, such as part numbers or SKUs. To preserve leading zeros, format the input range as text before pasting or typing values. Select the column or range where you want to paste the values ​​and then useCTRL+1createFormat > Cellsdialogue and aboutFirst NumberselectText. Now Excel will keep your leading zeros.

If you've already entered data and Excel has removed the leading zeros, you can use theTEXTFunction to add them back. You can reference and use the top cell with the values=TEXT(value,"00000"), where the number of zeros in the formula represents the total number of characters you want, copy and paste them into the rest of your range.

TEXT function - Microsoft Support (4)

If for some reason you need to convert text values ​​to numbers you can multiply by 1 e.g.=D4*1, or use the double unary operator (--) as=--D4.

Excel separates thousands with commas if the format contains a comma (,) enclosed in pound signs (#) or zeros. For example, if the format string"#, ###", Excel displays the number 12200000 as 12,200,000.

A comma after a digit placeholder scales the number by 1000. For example, if the format string"#,###.0,", Excel displays the number 12200000 as 12200.0.

TEXT function - Microsoft Support (5)


  • The thousands separator depends on your regional settings. In the US it is a comma, but in other countries it can be a period (.).

  • The thousands separator is available for number, currency, and accounting formats.

Below are examples of standard numbers (thousands and decimal separators only), currency, and accounting formats. The currency format allows you to enter the currency symbol of your choice and align it next to its value, while the ratio format aligns the currency symbol to the left of the cell and the value to the right. Note the difference between currency and accounting format codes below, where accounting uses an asterisk (*) to create a separation between the symbol and the amount.

TEXT function - Microsoft Support (6)

To find the format code of a currency symbol, first pressCtrl+1(oTEXT function - Microsoft Support (7)+1on Mac), choose the format you want, then choose an iconSymbolCollapsible list:

TEXT function - Microsoft Support (8)

then clickFantasialeft ofCategoryand copy the format code that includes the currency symbol.

TEXT function - Microsoft Support (9)

Use:The TEXT function does not support color format. So if you copy a number format code from the Format Cells dialog box that contains a color, like this: $#,##0.00_);[Putrefaction]($#,##0.00), the TEXT function accepts the format code but does not display the color.

You can change the way a date is displayed using a combination of "M" for month, "D" for days, and "Y" for years.

TEXT function - Microsoft Support (10)

The format codes in the TEXT function are not case sensitive, so you can use "M" or "m", "D" or "d", "Y" or "y".

TEXT function - Microsoft Support (11)

make recommendations...

If you share Excel files and reports with users in different countries, you might want to provide them with a report in their language.MVP do Excel, Mynda Treacyhave a great solution for thatExcel data displayed in different languagesArticle. It also includes a sample workbook that you can download.

You can change the time display using a combination of "H" for hours, "M" for minutes or "S" for seconds and "AM/PM" for a 12-hour clock.

TEXT function - Microsoft Support (12)

If you omit "AM/PM" or "A/P", the time will be displayed in 24-hour format.

The format codes in the TEXT function are case insensitive, so you get "H" or "h", "M" or "m", "S" or "s", "AM/PM" or "am/Watch ".

You can change how decimal values ​​are displayed using percentage (%) formats.

TEXT function - Microsoft Support (13)

You can change the display of decimal values ​​with fractional formats (?/?).

TEXT function - Microsoft Support (14)

Cientific notationis a way of displaying numbers as a decimal between 1 and 10 multiplied by a power of 10. It is often used to condense the representation of large numbers.

TEXT function - Microsoft Support (15)

Excel provides 4 special formats:

  • CEP - "00000"

  • CEP + 4 - "00000-0000"

  • Phone number: "[<=9999999]###-####;(###)###-####"

  • Social Security Number - "000-00-0000"

TEXT function - Microsoft Support (16)

Special formats vary by location, but if there is no special format for your location or if it does not meet your needs, you can create your own viacell format>FantasiaDialogue.

common scenario

DieTEXTThe feature is rarely used alone and is mostly used in conjunction with something else. Suppose you want to combine text and a numeric value, for example, "Report printed on: 3/14/12" or "Weekly income: $66,348.72". You can type this into Excel manually, but that defeats the purpose of letting Excel do it for you. Unfortunately, when you combine text and numbers with formatting like date, time, currency, etc., Excel doesn't know how you want to display them, so the number formatting is wiped out. Here he isTEXTThe function is invaluable because you can force Excel to format the values ​​the way you want using aformat code, as"MM/DD/AA"for the date format.

The following example shows what happens when you try to join text and a number without using theTEXTOccupation. In this case we use theand commercial(&) to concatenate a string of text, a space (" ") and a value with=A2&" "&B2.

TEXT function - Microsoft Support (17)

As you can see, Excel has removed the date formatting in cell B2. The following example shows how to use the TEXT function to apply the desired formatting.

TEXT function - Microsoft Support (18)

Our updated formula is:

  • C2 cell:=A2&" "&TEXTO(B2,"MM/DD/AA")- Date format

common questions

Unfortunately you can't do that to him.TEXTfunction, you must use VBA (Visual Basic for Applications) code. The following link has a method:How to Convert a Numeric Value to English Words in Excel

yes you can use theseHIGHER, HIGHER,LOWERyRIGHTfunctions For example, =TOP("hello") would return "HELLO".

Yes, but it requires a few steps. First select the cell or cells where you want this to happen and use thatCtrl+1createFormat > Cellsdialogue tooAlignment > Text Control> check thosenew linePossibility. Then adjust your conclusionsTEXTFunction to include ASCII functionSIGN(10)where you want the line break. You may need to adjust the column widths depending on how the final result will line up.

TEXT function - Microsoft Support (19)

In this case we use:="Today is: "&CHAR(10)&TEXT(HOY(),"mm/dd/yy")

CalledCientific notationand Excel automatically converts numbers longer than 12 digits if one or more cells have the formatIn generaland 15 digits if one or more cells are formatted asnumber. If you need to enter long number strings but don't want to convert them, format the cells in question asTextbefore entering or pasting your values ​​into Excel.

TEXT function - Microsoft Support (20)

make recommendations...

If you share Excel files and reports with users in different countries, you might want to provide them with a report in their language.MVP do Excel, Mynda Treacyhave a great solution for thatExcel data displayed in different languagesArticle. It also includes a sample workbook that you can download.

See too

Create or delete a custom number format

Convert numbers stored as text to numbers

All Excel functions (by category)

Top Articles
Latest Posts
Article information

Author: Golda Nolan II

Last Updated: 03/07/2023

Views: 6566

Rating: 4.8 / 5 (78 voted)

Reviews: 85% of readers found this page helpful

Author information

Name: Golda Nolan II

Birthday: 1998-05-14

Address: Suite 369 9754 Roberts Pines, West Benitaburgh, NM 69180-7958

Phone: +522993866487

Job: Sales Executive

Hobby: Worldbuilding, Shopping, Quilting, Cooking, Homebrewing, Leather crafting, Pet

Introduction: My name is Golda Nolan II, I am a thoughtful, clever, cute, jolly, brave, powerful, splendid person who loves writing and wants to share my knowledge and understanding with you.