# 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

1. THOUSANDS SEPARATOR

2. NUMBER, CURRENCY, AND ACCOUNTING FORMAT

3. PERCENTAGE FORMAT

4. DATE FORMATS: MONTHS, DAYS, AND YEARS FORMAT

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

1. =TEXT(A2,"#,###") // thousands separator (,) without decimals

• A2 = 10000000 // data cell

• Result = 10,000,000

2. =TEXT(A2,"0,000.0") // thousands separator (,) with 1 decimals

• A2 = 10000000 // data cell

• Result = 10,000,000.0

3. =TEXT(A2,"0,000.00") // thousands separator (,) with 2 decimals

• A2 = 10000000 // data cell

• Result = 10,000,000.00

4. =TEXT(A2,"####") // without decimals

• A2 = 10000000 // data cell

• Result = 10000000

5. =TEXT(A2,"#,###, k") // thousands separator (,) with k

• A2 = 10000000 // data cell

• Result = 10,000 k

6. =TEXT(A2, "#,##0.00,, ""m""") // thousands separator (,) with 2 decimals in m (million)

• A2 = 10000000 // data cell

• Result = 10.00 m

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

1. =TEXT(A2,"0.00") // Number - General

• A2 = 1234.56 // data cell

• Result = 1234.56

2. =TEXT(A2,"#,##0") // Number - thousands separator (,) without decimals

• A2 = 1234.56 // data cell

• Result = 1,235

3. =TEXT(A2,"#,##0.00") // thousands separator (,) with 2 decimals

• A2 = 1234.56 // data cell

• Result = 1,234.56

4. =TEXT(A2,"\$#,##0") // currency - thousands separator (,) without decimals

• A2 = 1234.56 // data cell

• Result = \$1,235

5. =TEXT(A2,"\$#,##0.00") // currency - thousands separator (,) with 2 decimals

• A2 = 1234.56 // data cell

• Result = \$1,234.56

6. =TEXT(A2,"\$#,##0.00_);(\$#,##0.00)") // negative currency value - thousands separator (,) with 2 decimals

• A2 = 1234.56 // data cell

• Result = (\$1,234.56)

7. =TEXT(A2,"\$ * #,##0") // accounting - thousands separator (,) without decimals

• A2 = 1234.56 // data cell

• Result = \$ 1,235

8. =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

1. =TEXT(A2,"0%") // percentage without decimals

• A2 = 0.1011 // data cell

• Result = 10%

2. =TEXT(A2,"0.0%") // percentage with 1 decimal

• A2 = 0.1011 // data cell

• Result = 10.1%

3. =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

1. =TEXT(A2,"m") // month value (1â€“12)

• A2 = 12/04/2021 // data cell

• Result = 4

2. =TEXT(A2,"mm") // month value (01â€“12)

• A2 = 12/04/2021 // data cell

• Result = 04

3. =TEXT(A2,"mmm") // shorter month name (Janâ€“Dec)

• A2 = 12/04/2021 // data cell

• Result = Apr

4. =TEXT(A2,"mmmm") // month name (Januaryâ€“December)

• A2 = 12/04/2021 // data cell

• Result = April

5. =TEXT(A2,"mmmmm") // initial letter of the month name (Jâ€“D)

• A2 = 12/04/2021 // data cell

• Result = A

6. =TEXT(A2,"d") // date (1â€“31)

• A2 = 12/04/2021 // data cell

• Result = 12

7. =TEXT(A2,"dd") // date (01â€“31)

• A2 = 12/04/2021 // data cell

• Result = 12

8. =TEXT(A2,"ddd") // shorter weekday name (Sunâ€“Sat)

• A2 = 12/04/2021 // data cell

• Result = Mon

9. =TEXT(A2,"dddd") // weekday name (Sundayâ€“Saturday)

• A2 = 12/04/2021 // data cell

• Result = Monday

10. =TEXT(A2,"yy") // year in 2 digits (00â€“99)

• A2 = 12/04/2021 // data cell

• Result = 21

11. =TEXT(A2,"yyyy") // year in 4 digits (0000â€“9999)

• A2 = 12/04/2021 // data cell

• Result = 2021

12. =TEXT(A2,"mm yy") // month and year in 2 digits (Janâ€“Dec 00â€“99)

• A2 = 12/04/2021 // data cell

• Result = Apr 21

13. =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

1. =TEXT(B1,"h") // hours (0-23)

• A2 = 12/04/2021 20:42:27 // data cell as date and time

• Result = 20

2. =TEXT(B1,"hh") // hours (00-23)

• A2 = 12/04/2021 20:42:27 // data cell as date and time

• Result = 20

3. =TEXT(B1,"m") // minutes (0-59)

• A2 = 12/04/2021 20:42:27 // data cell as date and time

• Result = 4

4. =TEXT(B1,"mm") // minutes (00-59)

• A2 = 12/04/2021 20:42:27 // data cell as date and time

• Result =04

5. =TEXT(B1,"s") // seconds (0-59)

• A2 = 12/04/2021 20:42:27 // data cell as date and time

• Result = 27

6. =TEXT(B1,"ss") // seconds (00-59)

• A2 = 12/04/2021 20:42:27 // data cell as date and time

• Result = 27

7. =TEXT(B1,"h AM/PM") // hours (am/pm)

• A2 = 12/04/2021 20:42:27 // data cell as date and time

• Result = 8 pm

8. =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

9. =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

10. =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