Excel Text Function With Examples, Formula and Tips To Use

This tutorial will describe what is text function in excel, where and when it is used along with are examples and tips in utilizing this function.

Generally, Microsoft Excel is about data analysis and numbers calculations however there are also instances where it requires Text as a result or a number should be converted to text.

What is text function excel?

The TEXT function in Excel allows users to alter the numbers on how they are displayed by using format codes in formatting. It is a beneficial function in certain cases where you want to display numbers in a more readable format or combine text or symbols.


=TEXT(value, format_tex


valueRequiredValue in a numeric form that has to be formatted.
format_textRequiredSpecified number format

Return value 

Return the Text format of a number.


Convert a number to text in a number format

Applies To

  • Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

When is the Excel TEXT Function required?

Excel text function is required when in the following cases:

  1. It is used when displaying dates in a specified format.
  2. It is used when displaying numbers in a specified format or in a more legible way.
  3. It is used when you want to combine numbers with text or characters.

function text excel Example

Here are examples of Text function along with a description.

=TEXT(1234.567,“$#,##0.00”)The formula will display a currency which has a separator in every thousand and 2 decimals such as $2468.24. The decimal place of this formula round the value into two places.
=TEXT(TODAY(),“MM/DD/YY”)This will return the date today in MM/DD/YY format, like 12/15/2022.
=TEXT(TODAY(),“DDDD”)This return the day of today’s week, which is Thursday.
=TEXT(NOW(),“H:MM AM/PM”)The formula value is the current time, like 10:40 AM.
=TEXT(0.30,“0.0%”)Return percentage value, which is 30%.
=TEXT(5.75 ,“# ?/?”)Return fraction value, like 5 3/4.
=TEXT(13300000,“0.00E+00”)Scientific notation, like 1.33E+07
=TEXT(2468101112,“[<=9999999]###-####;(###) ###-####”)Special (Phone number), like(246) 810-1112
=TEXT(45678,“0000000”)Add leading zeros (0), like 0045678
=TEXT(246810,“##0° 00′ 00””)Custom – Longitude/Latitude/like 24° 68′ 10”

Excel TEXT function format codes

The text function as you observe is very simple however the tricky part of this function supplying the proper code format which will display the format you want. Basically, the TEXT function accepts most of the format codes used in Excel number formats.

The table below lists the most common and frequently used ones.

CodeDescriptionFormat code example
0Digit placeholder that displays insignificant zeros.#.00 – It always round to 2 decimal places. For instance, if the data is 1.5 this will display 1.50
, (comma)This is used as Thousands separator.###,###.## – It a thousand separator such as the 21500 this will display 21,500.00 with two decimal places.
#Digit placeholder that does not display extra zeros.#.## – shows up to 2 decimal places
?Generally used to align numbers in a column at a decimal point. This digit placeholder gives a space for insignificant zeros however it does not appear. #.?? – shows a maximum of 2 decimal places and aligns the decimal points in a column.
. (period)Decimal point 

Tips in using excel text functions

Additionally, you can include any of the following characters in the format code, and they will be displayed exactly as entered. In addition to the format codes and examples shown above you can also add other characters wherein it used to display what exactly you input.

+ and –Plus and minus signs
( )Open and Close parenthesis
{ }Curly brackets
< >Less than and greater than signs
=Equal sign
/Forward slash
!Exclamation point
Space character

How to use text Function in Excel

1. Basic example

With the example data below, we will utilize the Text function returns the output that converts the “d mmmm, yyyy” form. Take a look at the result below.

Basic text function example
Basic text function example

2. Excel TEXT with other functions

This time we will include CONCATENATE function with text function which will output the average of the student. This work as you can see below:

Text function with other functions
Text function with other functions

4. Adding zeros before numbers

Excel’s basic task is to remove leading zeros automatically, however if you need those zeros there is a Text function where it comes easy to do the task.

For instance you are having a task to enter codes which have leading of zeros but excel keeps removing it when you enter. Hence when you use Text function this will convert into the format you want.

Text function with leading zero

5. Converting telephone numbers to a specific format

If your task is to encode telephone or cellphone numbers with an exact format such as involving dash or parenthesis this is easy.

Here, I want to provide an example which you will see how it works. Hence the formula used is (##) ### ### ###. The # is the number of digits we wish to use.

text covert telephone number
text covert telephone number

If the Excel TEXT function isn’t working

If the formula of text does not work and displays an error “#NAME?” it is because we often forgot the quotation mark on the format code.

Here is an example to understand this:

If the formula is entered like this =Text(B3, mm dd, yyyy). It will display the error because it is incorrect thus, the formula should be enter this way: =TEXT(B3,”mm dd, yyyy”).


In conclusion, this article about the text function of excel is critical in working on every worksheet converting the numbers into text if requires.

Hence visit some of our tutorials if you’ve missed them. If you have any clarification and suggestions regarding this topic feel free to leave your comment below.

Thank you for reading 🙂

Leave a Comment