This tutorial demonstrates how to use the Excel Text Function in Excel.

TEXT Main

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:

TEXT formula syntax

(Notice how the formula inputs appear)

TEXT function Syntax and inputs:

=TEXT(value, format_text)

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")

Pic 01

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.

PIC Format

 

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

PIC 02

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")

PIC 03

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.

=TEXT(B3,"ddd")

PIC 04

More example of  TEXT function with Date

In below table shown more example which help us to exact different information from date.

Text 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."

PIC 05

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."

PIC 06

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(B3,"0000")

PIC 07

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."

PIC 08

You can use:

="There was a "&TEXT(B3,"0%")&" decrease in cost."

PIC 09

More example of  TEXT function with Number

In below table shown more example which help us to exact different format from number.

Text 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(A2,"[<=9999999]###-####;(###) ###-####")

Convert to Phone Number

TEXT in Google Sheets

The TEXT Function works exactly the same in Google Sheets as in Excel:

Text Google Sheet

 

TEXT Examples in VBA

vba code generator
You can also use the TEXT function in VBA. Type:
Application.Worksheetfunction.Text(value, format_text)
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