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

IF Function: Introduction The IF function is probably one of the most used Excel functions because it is easy to understand and very flexible when you apply it to real life situations.Here I will show you a couple of ways that you can use the IF function to get you up and going.DOWNLOAD WORKBOOK ...
RANDBETWEEN Function I use the RANDBETWEEN function all the time whenever I need to create a sample data set.The cool thing about the RANDBETWEEN function is that if you don't like the numbers that it has given you, you can press F9 in a cell and it will give you new numbers.  Try it out by downl...
SUMIF Function: One Criteria & Sum Range The SUMIF function can also be used to apply the criteria to one range and sum the corresponding values in a different range.So if you have a list of Sales Reps in one list and their Sales in another list and want to Sum the sales of only one of the Sales Reps, then the SUMIF...
Sum the Last 7 Transactions with the Offset Functi... What does it do?It returns a reference to a range, from a starting point to a specified number of rows, columns, height and width of cellsFormula breakdown:=OFFSET(reference, rows, columns, , )What it means:=OFFSET(start in this cell, go up/down a number of ro...