Return to List of Excel Functions

TEXT Function Examples – Excel, VBA, & Google Sheets

Download Example Workbook

Download the example workbook

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

TEXT Formula Main

 

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

The TEXT Function works similarly to cell formatting. 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 (or shortcut CTRL + 1). You can see a sampling of the formatting codes available to you.

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

In Excel, dates are stored as serial numbers (ex. 44022) They are merely formatted as dates. Hence, if you combine a string (text) and the cell with date, it will show up as a serial number.

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

This table shows more examples of Date and Time formatting.

Text Date

TEXT for Comma Separated Values

Similar to dates, numbers with commas 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 to Phone Number Formatting

To convert text to phone number formatting, using something like this (Note Example shows US Phone Number formatting, but you can adapt as needed):

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.

=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

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.