TEXT Function Examples – Excel, VBA, & Google Sheets

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

 

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

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.

 

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

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.

=TEXT(B3,"ddd")

More example of  TEXT function with Date

This table shows more examples of Date and Time formatting.

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

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

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 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]###-####;(###) ###-####")

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