TEXT function in Excel and Google Sheets
Formula, examples, and how to use itThe 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
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
- SUMIF formulas and example
- SUMIFS formulas and example
- COUNTIF formulas and example
- IF formulas and example
- CONVERT formulas and example
- Sum by month
- SUMIF cells if contains part of a text string
- Sum total sales based on quantity & price
- Combine date and time
- Convert 1-12 to month name
- Dynamic current date and time
- Count blank cell
- Count cell between two values
- Combine two or more cell
- Extract left before first space
- Extract the nth words in a text string
- Flip the first and last name
- SUMIFS formulas and example
- TEXT formulas and example
- Change the negative number to zero
- Change negative to positive numbers
- Calculate percentage changes for containing zero or negative values
- Convert date to text
- Convert time to text
- Convert weekday string to number
- Combine two or more cells with a line break
- Combine two or more cells and transpose
- Count cells over 10 characters
- Count cells that are not blank
- Count if cells contain a specific text
- Count cells that begin with a specific text
- COUNTIF based on multiple criteria