What are some examples of great code
With the TEXTFunction by applying formatting using Format codes change the way numbers are displayed. This feature is useful in cases where you want to display numbers in a more readable format or combine them with text or symbols.
Note: The TEXT function converts numbers to text, which can make them difficult to refer to in later calculations. It is best to keep the original value in one cell and use the TEXT function in another cell. If you then have to create other formulas, you can always refer to the original value and not the result of the TEXT function.
TEXT(Value; text format)
The syntax of the TEXTFunction has the following arguments:
A numeric value to be converted to text
A text string that defines the formatting to be applied to the specified value
In its simplest form, the TEXT function says:
TEXT (value you want to format; "format code to be applied")
Below are some common examples that you can copy right into Excel and then experiment with yourself. Make sure that the format codes are enclosed in quotation marks.
= TEXT (1234,567;"#.##0,00 €")
Currency with thousands separator and 2 decimal places, e.g. B. € 1,234.57. Note that Excel rounds the value to 2 decimal places.
= TEXT (TODAY ();"DD.MM.YY")
Current date in the format DD.MM.YY, e.g. B. 03/14/12
= TEXT (TODAY ();"TTTT")
Current day of the week, e.g. B. Monday
= TEXT (NOW ();"H: MM AM / PM")
Current time, e.g. B. 1:29 PM
= TEXT (0.285;"0,0 %")
Percentage, e.g. B. 28.5%
= TEXT (4.34;"# ?/?")
Break, e.g. B. 4 1/3
= SMOOTH (TEXT (0.34;"# ?/?"))
Break, e.g. B. 1/3 In this case, the SMOOTH function removes the leading space created by the decimal value.
= TEXT (12200000;"0.00E + 00")
Scientific notation, e.g. B. 1.22E + 07
= TEXT (1234567898;"[<=9999999]###-####;(###) ###-####")
Special format (telephone number), e.g. B. (123) 456-7898
= TEXT (1234;"0000000")
Adding leading zeros (0), e.g. B. 0001234
= TEXT (123456;"##0° 00' 00''")
Custom - Latitude / Longitude
Note: While you can use the TEXT function to change the formatting, this is not the only option. You can also change the format without a formula. To do this, press CTRL + 1 (or +1 on a Mac), and then select in the dialog box Format cells the tab numbers out.
Download our examples
You can download a sample workbook that contains all of the TEXT functionality examples found in this article, as well as other examples. You can follow these examples or create your own format codes for the TEXT function.
Download examples of the TEXT function in Excel
Other format codes available
In the dialog box Format cells you can find more available format codes:
Press CTRL + 1 ( +1 on a Mac) to the Format cells to call.
Select on the tab numbers the desired format.
Select the option Custom out.
The desired format code is now in the field Type displayed. In this case, select in the field Type everything except the comma (,) and @ symbol. In the following example only DD.MM.YYYY was selected and copied.
Press CTRL + Cto copy the format code, then press Abortto bring up the dialog box Format cells close.
Now all you need is CTRL + V press to insert the format code into your TEXT formula, e.g. E.g .: = TEXT (B2; "DD.MM.YYYYRemember to put the format code in quotation marks ("format code"), otherwise Excel will issue an error message.
Format codes by category
Below are some examples of how different number formats could be applied to your values. Use the dialog box to do this Format cells and then the option Custom, around Format codes in your TEXT- Copy function.
- Select a number format
- Leading zeros (000)
- Show a thousand separator
- Number, currency and accounting formats
- Scientific notation
- Special formats
Why does Excel delete leading zeros?
Excel is designed to search cells for numbers rather than numbers that look like text (such as part or item numbers). To keep leading zeros, format the input area as text before inserting or entering values. Highlight the column or range in which you want to enter the values, press CTRL + 1to bring up the dialog box Format cells and select on the tab numbers the option text out. Now Excel keeps the leading zeros.
If Excel removed leading zeros after entering data, you can use the TEXT-Add function again. You can refer to the top cell with the values and = TEXT (value; "00000") where the number of zeros in the formula must match the number of characters you want. Then copy and paste the rest of the area.
If for some reason the text values need to be converted back to numbers, you can multiply by 1, e.g. B. = D4 * 1 or use the double unary operator (-), e.g. B. = - D4.
Excel separates thousands with periods when the format includes a period (.) Enclosed by number signs (#) or zeros. With the format string "#.###" for example, Excel displays the number 12,200,000 as 12,200,000.
A period following a placeholder for digits indicates a multiple of a thousand. With the format string "#.###,0." for example, Excel displays the number 12,200,000 as 12,200.0.
The thousands separator depends on the country settings. It is a comma in the US and a period (.) In other locales.
The thousand separator is available for number, currency, and accounting formats.
The following are examples of standard number formats (thousands separators and decimal numbers only), as well as currency and accounting formats. You can add the desired currency symbol to currency formats and align it next to the value, while in accounting formats the currency symbol and the decimal places are aligned in the column. Notice the differences between currency and accounting formats below. The accounting format uses a trailing space to separate the symbol from the value.
If you are looking for the format code for a currency symbol, press first CTRL + 1 (or +1 on a Mac), then select the format you want and finally from the drop-down list symbol the symbol from:
Then click to the left of the sectioncategory on Customand copy the format code including the currency symbol.
Note: The TEXT function does not support color formatting. So if you copy a number format code from the Format Cells dialog box that contains a color; B. "#. ## € 0.00;[Red]- #. ## € 0.00 ", the TEXT function accepts the format code, but does not display the color.
You can change how a date is displayed using a combination of "D" for day, "M" for month, and "Y" for year.
The format codes of the TEXT function are not case-sensitive. So you can use "T" or "t", "M" or "m", "J" or "j".
You can change the time display to use a combination of "H" for hours, "M" for minutes, or "S" for seconds and "AM / PM" for 12-hour clock display.
If you omit "AM / PM" or "A / P", the time is displayed in 24-hour format.
The format codes of the TEXT function are not case-sensitive. So you can use "H" or "h", "M" or "m", "S" or "s".
You can change the display from decimal values to percentages (%).
You can change the display of decimal values to fractions (? /?).
With scientific notation, numbers can be given as a decimal number between 1 and 10 with a power of ten. This notation is often used to shorten the display of large numbers.
Excel already contains some special formats:
Postal code - "00000"
Postal code D - "D-00000"
Telephone number - "[<= 9999999] ### - ####; (###) ### - ####"
Social Security Number - "0000-00 00 00"
The special formats depend on the locale. If there are no custom formats for your locale, or if the custom formats do not suit your needs, you can use the dialog box Format cells under Custom create your own special formats.
The TEXT-Function is rarely used alone, but mostly in conjunction with other information. For example, suppose you want to combine text and a numeric value, such as "Report printed 3/14/12" or "Weekly sales: $ 66,348.72". You could type this into Excel manually, but that doesn't make much sense as Excel can do this job for you. Unfortunately, when it comes to combining text and formatted numbers like dates, times, currency, etc., Excel doesn't know how to display them, so it discards the number format. At this point the TEXTFunction is invaluable as this function allows you to force Excel to format the values however you want, for example by using a Format code how "DD.MM.YY" use as date format.
The following example shows what happens if you type text and number without the TEXT-Try to combine the function. In this case it will ampersands (&) used to represent a text string, a space, and a value with = A2 & "" & B2 to concatenate.
As you can see, Excel has removed the formatting of the date in cell B2. The next example shows how to use the TEXT function to apply the desired format.
The updated formula is:
Cell C2:= A2 & "" & TEXT (B2; "DD.MM.YY") - Date format
frequently asked Questions
How can I convert numbers to text, such as 123 to one hundred and twenty three?
Can I change the spelling of text?
Yes. You can use the functions LARGE, SMALL and LARGE2 for this purpose. For example, = LARGE ("hello") would become "HELLO".
Can I insert a new line (line break) in a cell as with ALT + ENTER in the TEXT function?
Yes, but there are a few steps involved. First, select the appropriate cell or cells and use CTRL + 1to bring up the dialog box Format cells to open it, and then activate it under Alignment> Text Control the option new line. Next, adjust the finished one TEXTFunction on. Add the ASCII function at the point where the line break should occur CHARACTERS (10) a. You may need to adjust the column width to match the alignment of the end result.
In this case the following was used: = "Today's date:" & CHARACTER (10) & TEXT (TODAY (); "DD.MM.YY")
Why does Excel convert my number entries to 1.22E + 07 or something similar?
This is the so-called scientific notation. Excel automatically converts numbers longer than 12 digits when a cell is saved as a Generally is formatted, and with more than 15 digits when a cell is as number is formatted. If you need to enter long numeric strings but don't want them to be converted, format the cells in question as textbefore entering or pasting values in Excel.
Dates in different languages
Create or delete a custom number format
Convert numbers saved as text
All Excel functions (by category)
- When shows up bad
- Can humans and animals make babies?
- Is college free in Canada for citizens
- There was color television in the 1970s
- What are traditional Telugu foods
- Have you ever given up hope
- What are some entry level IT jobs
- Would you ever reopen Alcatraz Island?
- Some cats actually have Aspergers Syndrome
- Why is Vincent van Gogh a genius
- What does BRP mean in slang
- What do you call your grandmother
- Why can't we meet all other expectations
- What happens when particles stop moving
- Are you in school right now?
- What is the greatest sexual secret
- Which degrees belong to the MINT
- Men like to wear women's panties
- Can we have economic growth without pollution
- What do you eat for christmas
- Which countries make up Central America
- What happens after pregnancy
- Which side should one stay in Mauritius
- Do you like being venezuelan