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.
Syntax
=TEXT(value, format_tex
Argument
| ARGUMENT | REQUIRED/OPTIONAL | EXPLANATION |
|---|---|---|
| value | Required | Value in a numeric form that has to be formatted. |
| format_text | Required | Specified number format |
Return value
Return the Text format of a number.
Purpose
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:
- It is used when displaying dates in a specified format.
- It is used when displaying numbers in a specified format or in a more legible way.
- 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.
| Formula | 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.
| Code | Description | Format code example |
| 0 | Digit 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.
| Symbol | Description |
| + and – | Plus and minus signs |
| ( ) | Open and Close parenthesis |
| : | Colon |
| ^ | Caret |
| ‘ | Apostrophe |
| { } | Curly brackets |
| < > | Less than and greater than signs |
| = | Equal sign |
| / | Forward slash |
| ! | Exclamation point |
| & | Ampersand |
| ~ | Tilde |
| 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.
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:
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.
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.
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”).
Conclusion
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 🙂