How to use the Excel TEXT function to convert numbers into text

If you want to use numbers or dates in a text in Excel, sometimes a simple cell reference is not enough because the number or date is not displayed in the text format that you need. 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.

What is the Excel TEXT() function?

The TEXT() function is written as follows:

=TEXT(Value,format_text)

The Excel TEXT function requires two para­met­ers:

• Value: enter the data to be displayed as text, either as a direct entry or as a reference to the cell con­tain­ing the data.
• Format_text: tell Excel how you want to format the number.

If you don’t need special format­ting, 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 place­hold­er char­ac­ters that you can use to write format codes. We’ll go over these codes in more detail below.

Close

What are Excel Text format codes?

The Excel TEXT() function requires a format code. There are three different place­hold­ers available for this purpose. Each rep­res­ents one digit of the numerical value:

You use these place­hold­ers in com­bin­a­tion with certain text char­ac­ters. For example, you can display numbers with leading zeros, in­di­vidu­al thousands sep­ar­at­ors, telephone numbers or units of meas­ure­ment:

However, you can only use certain text char­ac­ters directly in the format codes:

In the next section, you can find out how to insert text into a format code.

How to add text to numbers

If you want to insert text char­ac­ters that Excel doesn’t recognise, you have to enter a backslash (\) before the 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 ‘meter’ after the number ‘100’ so that Excel outputs ‘100 meter’, you can do so with the following code:

=TEXT(100;"# \m\e\t\er")

You can also get Excel to write a number as a per­cent­age without mul­tiply­ing it by 100. For example, if you want to format the number 33 as ‘33%’, you can use the following code.

=TEXT(33;"###\%")

If you find the use of escape char­ac­ters too cum­ber­some, there are two al­tern­at­ives:

• Direct format­ting: 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.
• Con­cat­en­at­ing text: you can con­cat­en­ate text to add ad­di­tion­al text. To do so, you need to use the ‘&’ symbol. For example, if you want Excel to display ‘100 meter’, you would use the following code:

=TEXT(100;"#") & "meter"

What special control char­ac­ters are there for format codes?

In addition to place­hold­ers, there are a number of other control char­ac­ters available for format­ting text:

Which functions can be combined with Excel TEXT?

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

In addition to the functions mentioned above, Excel also offers numerous helpful functions that make working with the popular program much easier. In the following articles, we take a look at other functions in Excel and provide examples for how to use them.