What are some examples of great code

TEXT (function)

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.

syntax

TEXT(Value; text format)

The syntax of the TEXTFunction has the following arguments:

Argument name

description

value

A numeric value to be converted to text

Text format

A text string that defines the formatting to be applied to the specified value

Overview

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.

formula

description

= 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:

  1. Press CTRL + 1 ( +1 on a Mac) to the Format cells to call.

  2. Select on the tab numbers the desired format.

  3. Select the option Custom out.

  4. 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.

  5. Press CTRL + Cto copy the format code, then press Abortto bring up the dialog box Format cells close.

  6. 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
  • Select a number format
  • Leading zeros (000)
  • Show a thousand separator
  • Number, currency and accounting formats
  • Dates
  • times
  • Percentages
  • Fractions
  • 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.

Hints:

  • 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.

General scenario

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

See also

Create or delete a custom number format

Convert numbers saved as text

All Excel functions (by category)