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.

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

DOWNLOAD WORKBOOK

Text

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”

 

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

Share on Google+

Related Posts

Count Text Occurrences with Excel’s SUBSTITU... What does it do?It replaces specific text in a text string i.e. Substitutes new text for old text in a text string.Formula breakdown:=SUBSTITUTE(text, old_text, new_text, )What it means:=SUBSTITUTE(This cell, From this TEXT character, To this TEXT character, )...
In-Cell Bar Charts with the REPT Function When you are creating an Excel Dashboard and are limited by space and do not want to insert a chart, you can easily create an in-cell bar chart using the RPT (repeat) function.The RPT function uses the vertical bar character | as the first argument: text and references the va...
Free Excel Formulas & Functions Webinar Train... In this popular Free Excel Formulas & Functions Webinar Training which goes for 60 minutes you will learn the MUST KNOW Excel Formulas to ADVANCE your Excel skills!You will learn the following Excel Formulas & Functions: IF, SUMIF, VLOOKUP, INDEX/MATCH + MORE...!P...
IFERROR Function: Introduction If you have a calculation that results in an error like, #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, then you can clean it up by using the IFERROR function which allows you to replace the error it with a 0 or a blank cell.I show you both IFERROR examples below.DOWNLOAD ...