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.
Syntax
TEXT(Wert, format_text)
DieTEXTThe function syntax has the following arguments:
| argument name | Description |
| Wert | A numeric value to convert to text. |
| text_format | A text string that defines the format you want to apply to the specified value. |
overview
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.
| Formula | Description |
| =TEXT(1234,567,"$#,##0,00") | Currency with thousands separator and 2 decimal places, p. B. $1,234.57. Note that Excel rounds the value to 2 decimal places. |
| =TEXT(TODAY(),"MM/DD/AA") | Today's date in MM/DD/YY format, eg. B. 03/14/12 |
| =TEXT(TODAY(),"DDDD") | Today's day of the week, like Monday. |
| =TEXT(NOW(),„H:MM AM/PM“) | 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. |
| =TEXT(12200000,"0,00E+00") | Scientific notation, such as 1.22E+07 |
| =TEXT(1234567898,"[<=9999999]###-####;(###) ###-####") | Special (phone) such as (123) 456-7898 |
| =TEXT(1234,"0000000") | 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(o
+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:
-
PressCtrl+1(
+1on Mac) to open thecell formatDialogue. -
Select the format you wantnumberEyelash.
-
ChooseFantasiaPossibility,
-
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.
-
PressStrg+Cto copy the format code, then pressCanceldismissed themcell formatDialogue.
-
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.
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
- 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.
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.
Comments:
-
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.
To find the format code of a currency symbol, first pressCtrl+1(o+1on Mac), choose the format you want, then choose an iconSymbolCollapsible list:
then clickFantasialeft ofCategoryand copy the format code that includes the currency symbol.
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.
The format codes in the TEXT function are not case sensitive, so you can use "M" or "m", "D" or "d", "Y" or "y".
| | 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.
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.
You can change the display of decimal values with fractional formats (?/?).
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.
Excel provides 4 special formats:
-
CEP - "00000"
-
CEP + 4 - "00000-0000"
-
Phone number: "[<=9999999]###-####;(###)###-####"
-
Social Security Number - "000-00-0000"
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.
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.
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.
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.
| | 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)