- Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.
The purpose of the blog post is to help you learn how to use the TEXT function. With the TEXT function, you can modify how a number looks by using format codes to set formatting to it. These can be hugely resourceful during times when you want to present numbers in a clearer format. Alternatively, if you want to integrate numbers with text or symbols.
Note: Since the TEXT function will convert numbers to text, it could be make it tougher to reference in further calculations. Ideally, it’s best practice to retain your first value in one cell, then apply the TEXT function in a different cell. Following this, if you must create other formulas, constantly reference the initial value rather than the TEXT function result.
Syntax of TEXT Function
The displayed arguments form the TEXT syntax:
|value||Any numeric value that you prefer to be converted into text.|
|format_text||A text string that verifies the formatting that you wish to be transferred to the given value.|
Summary of TEXT Function
In its most basic form, the TEXT function means:
- =TEXT(Value you want to format, “Format code you want to apply”)
Listed below are some common examples that you can copy straight into Excel to test and play around with by yourself. Keep in mind the format codes being placed inside the quotation marks.
|=TEXT(1234.567,“$#,##0.00”)||Currency including a thousands separator and 2 decimals, such as $1,234.57. Remember that Excel always rounds the value to 2 decimal places by default.|
|=TEXT(TODAY(),“MM/DD/YY”)||Today’s date in MM/DD/YY format, such as 04/08/20.|
|=TEXT(TODAY(),“DDDD”)||Today’s day of the week, like Tuesday.|
|=TEXT(NOW(),“H:MM AM/PM”)||Present time, such as 1:29 PM.|
|=TEXT(0.285,“0.0%”)||Percentage, such as 28.5%.|
|=TEXT(4.34 ,“# ?/?”)||Fraction, such as 4 1/3.|
|=TRIM(TEXT(0.34,“# ?/?”))||Fraction, like 1/3. Keep in mind that this applies the TRIM function to delete the leading space using a decimal value.|
|=TEXT(12200000,“0.00E+00”)||Scientific notation, such as 1.22E+07.|
|=TEXT(1234567898,“[<=9999999]###-####;(###) ###-####”)||Special (Phone number), like (123) 456-7898.|
|=TEXT(1234,“0000000”)||Insert leading zeros (0), like 0001234.|
|=TEXT(123456,“##0° 00′ 00””)||Custom – Latitude/Longitude.|
|456,“##0° 00′ 00””)||Custom – Latitude/Longitude|
Note: Even though you can employ the TEXT function to adjust formatting, there are also other ways to do so. For example, you can alter the format which skips the needs for a formula by pressing CTRL+1 (or +1 on the Mac). Then, you select your desired format from the Format Cells > Number dialogue.
Download our TEXT Function examples
You can download an example workbook which includes every TEXT function example presented in this post, with some added bonuses thrown in for good measure. Feel free to work alongside this, or devise your personal TEXT function format codes.
Download Excel TEXT function examples
Other format codes that are available
You can deploy the Format Cells dialogue to explore the alternative available format codes:
- Press Ctrl+1 ( +1 on the Mac) to launch the Format Cells dialogue.
- Choose your preferred format from the Number tab.
- Pick the Custom option.
- Your preferred format code is now displayed in the Type box. For this case, pick everything within the Type box apart from the semicolon (;) and @ symbol. In the example underneath, we chose and copied only mm/dd/yy.
- Press Ctrl+C to duplicate the format code. Then, press Cancel to exit the Format Cells dialogue.
- From herein out, all you must do is press Ctrl+V to paste the format code into your TEXT formula, such as: =TEXT(B2,”mm/dd/yy“). Check that you paste the format code inside quotes (“format code”), or else Excel will display you an error message.
Frequently Asked Questions
How can I convert numbers to text, like 123 to One Hundred and Twenty-Three?
Inconveniently, the TEXT function does not allow you to do that. However, this can be achieved by using Visual Basic for Applications (VBA) code. See this link for a method: How to convert a numeric value into English words in Excel
Can I change case of text?
Yeah absolutely, you can employ the UPPER, LOWER, and PROPER functions. For instance, =UPPER(“awesome”) would return “AWESOME”.
Can I insert a new line (line break) with the TEXT function in exactly the same way I can with Alt+Enter in a cell?
Yes, you can although it takes quite a few steps. Start by choosing the cell or cells that you seek this to occur and use Ctrl+1 to reveal the Format > Cells dialogue, then Alignment > Text control > check the Wrap Text option. After this, modify your completed TEXT function to consider the ASCII function CHAR(10) in the exact area for where you prefer to have the line break. You may have to amend your column width which may vary according to how the final result lines up.
In this scenario, we used: =”Today is: “&CHAR(10)&TEXT(TODAY(),”mm/dd/yy”)
Why does Excel convert my number entries to something like this: 1.22E + 07?
This is known as Scientific Notation, and Excel will instantly convert numbers exceeding 12 digits if a cell(s) is set to General, and 15 digits if a cell(s) is formatted as a Number. If you have to type long numeric strings, without converting them, then format the relevant cells as Text prior to you entering or pasting your values into Excel.
Dates Displayed in Other Languages
You may want to consider providing them a report in whichever users’ countries’ respective language. Excel MVP, Mynda Treacy has a fantastic solution in this Excel Dates Displayed in Different Languages article. It even contains a sample workbook which you can download.