Excel Text: using the TEXT function to convert numbers into individually formatted text

Excel saves numbers and dates in an internal format. This is necessary so that the data can be used in formulas. If you want to use the data in text, sometimes a simple cell reference is not enough because the number or date is not displayed in the desired text format. In these cases, the Excel TEXT function comes in handy. It allows you to convert any data (e.g. numbers, dates, etc.) into text and format it as you wish.

Tip: Managing Office documents

The IONOS HiDrive Cloud Storage enables you to store, edit, and share your Office documents centrally and securely. Whether for private or professional purposes, you can work flexibly on any device with the HiDrive app and other interfaces. IONOS' state-of-the-art US data centers ensure your data is optimally protected.

The Excel TEXT() function

The TEXT() function is written as follows:

=TEXT(value,format_text)

The TEXT function requires two parameters:

  • Value: Enter the number to be displayed as text either directly or using a cell reference.
  • Format_text: Tell Excel how you want to format the number.

If you don’t require special formatting, you can simply enter a hashtag as part of the text format code, as shown in the following example:

=TEXT(88;"#")

The hashtag (“#”) is one of three placeholder characters that you can use to write format codes. More on that below.

Note

In cells, text is aligned to the left, while numbers are aligned to the right.

Text format codes

The Excel TEXT() function requires a format code. There are three different placeholders available for this purpose. Each represents one digit of the numerical value:

Placeholder Function
# Optional digit: ignored if the number contains fewer digits than the number of placeholder characters.
0 Zero placeholder: if no digit is available for this position, Excel will write a zero.
? Space placeholder: Only works with fractional numbers. Excel fills missing digits with spaces so that fractional numbers can be aligned with the decimal point or the fraction bar.

You use these placeholders in combination with certain text characters. For example, you can display numbers with leading zeros, individual thousands separators, telephone numbers or units of measurement:

Use case Numerical value Format code Text displayed
Fixed number of digits 123 “000000” 000123
Thousands separators 21350 “##,###” 21,350
Telephone number 4930200123456 “+## ## ### ###-###” +49 30 200 123-456
Unit of measurement (e.g. degree) 90 “#°” 90°

However, you can only use certain text characters directly in the format codes:

Characters Description
+ - Plus, minus
() {} Parentheses and curly brackets
$ € Currency symbols
. , : ! Punctuation: period, comma, colon, exclamation mark
^ ' ~ Caret, apostrophe, tilde
  Space
/ Forward slash
= Equal sign

Find out how to insert any text into the format code below.

Adding text to numbers

If you want to insert text characters that Excel doesn’t recognize, you must enter a backslash (“\”) before the respective text character. This backslash – referred to as an “escape character” – tells Excel to treat the following character as plain text and not as a symbol.

For example, if you want to add the word “meters” after the number so that Excel outputs “100 meters”, you can do so with the following code:

=TEXT(100;“# \m\e\t\e\r\s”)

You can also force Excel to write a number as a percentage without multiplying it by 100. For example, if you want to format the number 33 as “33%”, you would use the following code.

=TEXT(33;“###\%”)

If you find the use of escape characters too cumbersome, there are two alternatives:

Direct formatting: You can format the numerical value directly. In the “Format Cells – Custom” dialog box, you can integrate any text without using the escape character. In this dialog box, you can also find other format codes that cannot be used in the TEXT() function.

Concatenating text: You can concatenate text to add additional text. To do so, you will use the “&” symbol. For example, if you want Excel to display “100 meters”, you would use the following code.

=TEXT(100;“#”) & “meters"

Special control characters in format codes

In addition to placeholders, there are a number of other control characters available for formatting text:

Function Control characters Effect
Write as a fraction / If you insert a forward slash, Excel will convert the number to a fraction. You can also explicitly define the denominator which Excel will use to convert the number accordingly. For example, the number 40 then becomes “200/5”.
Format as a percentage % Multiplies the number by 100 and adds a percentage sign after it. For example, the number 0.6 becomes “60%”.
Conditional formatting [>=1000] In square brackets, you can specify a condition that must be met for Excel to apply the formatting. If the condition is not met, Excel converts the number as if you had only entered the format code “#”.
Tip

You can force Excel to treat cell entries as text by writing an apostrophe (’) as the first character. The apostrophe itself will not be visible in the cell.

Related text functions

You can add more functions to the TEXT() function or replace it partially with the following functions:

Function Description Example
=DOLLAR() Writes a number using the currency format =DOLLAR(40) outputs “$40.00”
=FIXED() Writes the number using a fixed number of decimal places =FIXED(12.34,4) outputs “12.3400”
=TRIM() Deletes spaces before/after the text =TRIM(“33”) outputs “33”
=UPPER() Capitalizes all letters in the text =UPPER(“excel”) outputs “EXCEL”
=PROPER() Capitalizes the first letter and leaves the rest in lowercase letters =PROPER(“excel”) outputs “Excel”

HiDrive Cloud Storage with IONOS

Your data is stored securely in data centers in the US and Europe. Access HiDrive from any device, any time, from the office or on the go.

Available anywhere
Shared access
Highly secure
We use cookies on our website to provide you with the best possible user experience. By continuing to use our website or services, you agree to their use. More Information.