What does it do?

Converts a numeric value to text and lets you specify the display formatting by using special format strings

Formula breakdown:

=TEXT(value, format text)

What it means:

=TEXT(a numeric value or a formula, a text string enclosed in quotation marks)


The TEXT function in Excel allows you to convert a numeric value to a specific format by using special format strings.

If you have a date and want to show just the month or if you have a large number and want to show it in a thousands format , then the TEXT function is your savior.

An example usage of the TEXT would be:

Following is a demonstration and a table of the different ways you can display a value in Excel by using the TEXT function:

DOWNLOAD EXCEL WORKBOOK

How to Use the Text Formula in Excel

 

To displayAsUse this format
1234.591234.6“####.#”
8.98.900“#.000”
0.6310.6“0.#”
1234.5681234.6“#.0#”
5.255 1/4“# ???/???”
1200012000“#,###”
1200012“#,”
1220000012.2“0.0,,”
Months1–12“m”
Months01–12“mm”
MonthsJan–Dec“mmm”
MonthsJanuary–December“mmmm”
MonthsJ–D“mmmmm”
Days1–31“d”
Days01–31“dd”
DaysSun–Sat“ddd”
DaysSunday–Saturday“dddd”
Years00–99“yy”
Years1900–9999“yyyy”
Hours0–23“h”
Hours00–23“hh”
Minutes0–59“m”
Minutes00–59“mm”
Seconds0–59“s”
Seconds00–59“ss”
Time4:00 AM“h AM/PM”
Time4:36 PM“h:mm AM/PM”
Time4:36:03 PM“h:mm:ss A/P”
Time36:03.8“h:mm:ss.00”

 

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn