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.
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.
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 “#”. |
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.
Related articles
How to use the Excel COUNT function
Analyses and calculations in Excel can be highly complex. Excel’s worksheets let you create huge tables, but sometimes you just want to answer a very simple question: How many cells in the table contain a number? In big worksheets, it can be virtually impossible to check the whole thing manually. The COUNT function in Excel was created specifically to solve this problem.
Faster with Excel: the VLOOKUP function explained
It can often be incredibly time-consuming to search for a specific entry in an Excel table by hand, which is where VLOOKUP comes into play. This practical function allows you to find the exact value for a specific search criterion. The VLOOKUP function is indispensable for managing price lists, members directories, and inventory catalogues. To ensure you can benefit from this practical function,…
Identifying Duplicates in Excel
There are several reasons why you may want to find duplicates in Excel – to filter out errors or analyze double entries. Excel can automatically display these duplicates to you. Thanks to color highlighting, you can instantly see which entries appear more than once in your tables. And the feature is easy to use: We’ll explain how it works.
Excel: conditional formatting – what is it?
Excel’s conditional formatting is a useful feature to visually distinguish and analyze your data. This type of Excel formatting allows you to emphasize cells according to certain conditions. Conditions are predefined using specified values or formulas. But how does it work? And how is conditional formatting applied in Excel?