Download the example workbook
In this Article
- Text Function Overview
- How to use the TEXT Function in Excel:
- String Combined with TEXT
- More example of TEXT function with Date
- More example of TEXT function with Number
- Convert values to phone numbers
- TEXT in Google Sheets
- TEXT Examples in VBA
This tutorial demonstrates how to use the Excel Text Function in Excel.
Text Function Overview
The TEXT function returns a value converted to text with a specified format.
To use the TEXT Excel Worksheet Function, select a cell and type:
(Notice how the formula inputs appear)
TEXT function Syntax and inputs:
value – The value to format
format_text – The text format to apply
How to use the TEXT Function in Excel:
The TEXT function takes a value (number) and converts it to a text with a particular format you choose.
=TEXT(B3,"mmm d, dddd")
TEXT function is mostly like formatting cells. So if you are unsure of what the second argument should be, search for it inside Format Cells.
For instance, right-click B3 and choose Format Cells. You can see all these Format Code you need to input.
But be aware that text doesn’t work well with calculations, so do take note if it is indeed necessary. Below are some scenarios you can use it.
String Combined with TEXT
If you don’t know yet, dates are stored as serial numbers. They are merely formatted to be presented as a date. Hence, if you combine a string (text) and the cell with date, it will show up as numbers.
="Goods were delivered on "& B3
Hence, we need to use the TEXT function to convert it from value to a text.
="Goods were delivered on "&TEXT(B3,"mmm d, dddd")
TEXT for Day of Week
You could need the day of week in your report and not the date itself, hence this could be useful.
More example of TEXT function with Date
In below table shown more example which help us to exact different information from date.
TEXT for Comma Separated Values
Similar to dates, comma separated numbers are merely a presentation. The actual value of B3 is 34000.4, but formatted to show thousand separators and 0 decimal places. Combining them would show this:
=B3&" of sales were made this month."
You could use TEXT again to ensure it has the thousand separators and 0 decimal places.
=TEXT(B3,"#,##0")&" of sales were made this month."
TEXT with Fixed Numbers
You might have a column of IDs you want to set a fix number to (eg. 4 digits). You could use:
TEXT with Percentages
Like dates and numbers with thousand separators, percentages are merely a presentation as well. The value behind 10% is 0.1 and you may not want it shown like this.
="There was a "& B3 &" decrease in cost."
You can use:
="There was a "&TEXT(B3,"0%")&" decrease in cost."
More example of TEXT function with Number
In below table shown more example which help us to exact different format from number.
Convert values to phone numbers
when we consider about US telephone number it contain 7 digits for local phone number and with country code it shown with 10 digits.
We can identify local number if less than 9999999, then we can use ###-#### format to shown local phone number, rest all we can shown with code in (###) ###-#### format.
The below function and screenshot shows this Excel Text formula.
TEXT in Google Sheets
The TEXT Function works exactly the same in Google Sheets as in Excel:
TEXT Examples in VBA
You can also use the TEXT function in VBA. Type:
For the function arguments (value, etc.), you can either enter them directly into the function, or define variables to use instead.
Return to the List of all Functions in Excel