TEXT function in Excel and Google Sheets

Formula, examples, and how to use it

The TEXT function lets you convert a number into a text string. =TEXT(Value you want to format, "Format you want to apply").

Why is the TEXT Function required?

TEXT function becomes handy especially when you have CONCATENATE/combine number and a text, for example, the 10% are stored as 0.1 in Excel or Google Sheets and this is where you would force to display as 10% with TEXT formula.

To display dates in a specified format
To display numbers in a specified format
To combine numbers with text or characters

Check below for various TEXT function and formula examples

THOUSANDS SEPARATOR
NUMBER, CURRENCY, AND ACCOUNTING FORMAT
PERCENTAGE FORMAT
DATE FORMATS: MONTHS, DAYS, AND YEARS FORMAT
TIME FORMATS: HOURS, MINUTES, SECONDS

TEXT FORMAT EXAMPLE AND RESULT — CLICK ON THE TAB TO SEE RESPECTIVE FORMAT

COMBINE DATE AND TIME

Thousands separator format in Excel and Google Sheets

How to use a thousand separators with the TEXT function in Excel and Google Sheets?

THOUSANDS SEPARATOR — EXCEL/GOOGLE SHEETS FORMULA

=TEXT(A2,"#,###") // thousands separator (,) without decimals
- A2 = 10000000 // data cell
- Result = 10,000,000
=TEXT(A2,"0,000.0") // thousands separator (,) with 1 decimals
- A2 = 10000000 // data cell
- Result = 10,000,000.0
=TEXT(A2,"0,000.00") // thousands separator (,) with 2 decimals
- A2 = 10000000 // data cell
- Result = 10,000,000.00
=TEXT(A2,"####") // without decimals
- A2 = 10000000 // data cell
- Result = 10000000
=TEXT(A2,"#,###, k") // thousands separator (,) with k
- A2 = 10000000 // data cell
- Result = 10,000 k
=TEXT(A2, "#,##0.00,, ""m""") // thousands separator (,) with 2 decimals in m (million)
- A2 = 10000000 // data cell
- Result = 10.00 m
=TEXT(A2, "#,##0.00,,, ""b""") // thousands separator (,) with 2 decimals in b (billion)
- A2 = 10000000 // data cell
- Result = 00.01 b

Number, currency, and accounting format in Excel and Google Sheets

How to apply number, currency and accounting formats with the TEXT function in Excel and Google Sheets?

NUMBER, CURRENCY, AND ACCOUNTING FORMAT — EXCEL/GOOGLE SHEETS FORMULA

=TEXT(A2,"0.00") // Number - General
- A2 = 1234.56 // data cell
- Result = 1234.56
=TEXT(A2,"#,##0") // Number - thousands separator (,) without decimals
- A2 = 1234.56 // data cell
- Result = 1,235
=TEXT(A2,"#,##0.00") // thousands separator (,) with 2 decimals
- A2 = 1234.56 // data cell
- Result = 1,234.56
=TEXT(A2,"$#,##0") // currency - thousands separator (,) without decimals
- A2 = 1234.56 // data cell
- Result = $1,235
=TEXT(A2,"$#,##0.00") // currency - thousands separator (,) with 2 decimals
- A2 = 1234.56 // data cell
- Result = $1,234.56
=TEXT(A2,"$#,##0.00_);($#,##0.00)") // negative currency value - thousands separator (,) with 2 decimals
- A2 = 1234.56 // data cell
- Result = ($1,234.56)
=TEXT(A2,"$ * #,##0") // accounting - thousands separator (,) without decimals
- A2 = 1234.56 // data cell
- Result = $ 1,235
=TEXT(A2,"$ * #,##0.00") // accounting - thousands separator (,) with 2 decimals
- A2 = 1234.56 // data cell
- Result = $ 1,234.56

Percentage format in Excel and Google Sheets

How to apply percentage formats with the TEXT function in Excel and Google Sheets?

PERCENTAGE FORMAT — EXCEL/GOOGLE SHEETS FORMULA

=TEXT(A2,"0%") // percentage without decimals
- A2 = 0.1011 // data cell
- Result = 10%
=TEXT(A2,"0.0%") // percentage with 1 decimal
- A2 = 0.1011 // data cell
- Result = 10.1%
=TEXT(A2,"0.00%") // percentage with 2 decimals
- A2 = 0.1011 // data cell
- Result = 10.11%

Date formats, months, days, and years in Excel and Google Sheets

How to apply date & and time formats with the TEXT function in Excel and Google Sheets?

DATE FORMATS: MONTHS, DAYS, AND YEARS FORMAT — EXCEL/GOOGLE SHEETS FORMULA

=TEXT(A2,"m") // month value (1–12)
- A2 = 12/04/2021 // data cell
- Result = 4
=TEXT(A2,"mm") // month value (01–12)
- A2 = 12/04/2021 // data cell
- Result = 04
=TEXT(A2,"mmm") // shorter month name (Jan–Dec)
- A2 = 12/04/2021 // data cell
- Result = Apr
=TEXT(A2,"mmmm") // month name (January–December)
- A2 = 12/04/2021 // data cell
- Result = April
=TEXT(A2,"mmmmm") // initial letter of the month name (J–D)
- A2 = 12/04/2021 // data cell
- Result = A
=TEXT(A2,"d") // date (1–31)
- A2 = 12/04/2021 // data cell
- Result = 12
=TEXT(A2,"dd") // date (01–31)
- A2 = 12/04/2021 // data cell
- Result = 12
=TEXT(A2,"ddd") // shorter weekday name (Sun–Sat)
- A2 = 12/04/2021 // data cell
- Result = Mon
=TEXT(A2,"dddd") // weekday name (Sunday–Saturday)
- A2 = 12/04/2021 // data cell
- Result = Monday
=TEXT(A2,"yy") // year in 2 digits (00–99)
- A2 = 12/04/2021 // data cell
- Result = 21
=TEXT(A2,"yyyy") // year in 4 digits (0000–9999)
- A2 = 12/04/2021 // data cell
- Result = 2021
=TEXT(A2,"mm yy") // month and year in 2 digits (Jan–Dec 00–99)
- A2 = 12/04/2021 // data cell
- Result = Apr 21
=TEXT(A2,"mmmm yyyy") // month and year in 4 digits (January–December 0000–9999)
- A2 = 12/04/2021 // data cell
- Result = April 2021

Time formats hours, minutes, seconds in Excel and Google Sheets

How to apply hours, minutes and second formats with the TEXT function in Excel and Google Sheets?

TIME FORMATS: HOURS, MINUTES, SECONDS — EXCEL/GOOGLE SHEETS FORMULA

=TEXT(B1,"h") // hours (0-23)
- A2 = 12/04/2021 20:42:27 // data cell as date and time
- Result = 20
=TEXT(B1,"hh") // hours (00-23)
- A2 = 12/04/2021 20:42:27 // data cell as date and time
- Result = 20
=TEXT(B1,"m") // minutes (0-59)
- A2 = 12/04/2021 20:42:27 // data cell as date and time
- Result = 4
=TEXT(B1,"mm") // minutes (00-59)
- A2 = 12/04/2021 20:42:27 // data cell as date and time
- Result =04
=TEXT(B1,"s") // seconds (0-59)
- A2 = 12/04/2021 20:42:27 // data cell as date and time
- Result = 27
=TEXT(B1,"ss") // seconds (00-59)
- A2 = 12/04/2021 20:42:27 // data cell as date and time
- Result = 27
=TEXT(B1,"h AM/PM") // hours (am/pm)
- A2 = 12/04/2021 20:42:27 // data cell as date and time
- Result = 8 pm
=TEXT(B1,"h:mm AM/PM") // hours and minutes (am/pm)
- A2 = 12/04/2021 20:42:27 // data cell as date and time
- Result = 8:42 pm
=TEXT(B1,"h:mm:ss A/P") // hours, minutes and seconds (A/P)
- A2 = 12/04/2021 20:42:27 // data cell as date and time
- Result = 8:42:27 P
=TEXT(B1,"hh:mm:ss") // hours, minutes and seconds
- A2 = 12/04/2021 20:42:27 // data cell as date and time
- Result = 20:42:27

Other widely used Excel and Google Sheets formulas explained

Others Formulas

Date and time functions

Number based criteria

Text-based criteria

Extract functions

Coming | Subscribe here for the custom Excel/Sheets formulas E-book (PDF) >

Google Sites

Report abuse