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 display As Use this format
1234.59 1234.6 “####.#”
8.9 8.900 “#.000”
0.631 0.6 “0.#”
1234.568 1234.6 “#.0#”
5.25 5 1/4 “# ???/???”
12000 12000 “#,###”
12000 12 “#,”
12200000 12.2 “0.0,,”
Months 1–12 “m”
Months 01–12 “mm”
Months Jan–Dec “mmm”
Months January–December “mmmm”
Months J–D “mmmmm”
Days 1–31 “d”
Days 01–31 “dd”
Days Sun–Sat “ddd”
Days Sunday–Saturday “dddd”
Years 00–99 “yy”
Years 1900–9999 “yyyy”
Hours 0–23 “h”
Hours 00–23 “hh”
Minutes 0–59 “m”
Minutes 00–59 “mm”
Seconds 0–59 “s”
Seconds 00–59 “ss”
Time 4:00 AM “h AM/PM”
Time 4:36 PM “h:mm AM/PM”
Time 4:36:03 PM “h:mm:ss A/P”
Time 36:03.8 “h:mm:ss.00”

 

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Create a Random List with Excel’s RAND Formu... What does it do? Gives you a random number between 0 and 1 Formula breakdown: =RAND() What it means: =RAND(Will automatically choose a random number between 0 and 1) Excel is able to do a lot of things that most users are unaware of!  One thing that amazes m...
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 ...
Add Comma After the Surname with Excel’s SUB... 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, )...
VLOOKUP Example: Vlookup with a Drop Down List What does it do? Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array. Formula breakdown: =VLOOKUP(lookup_value, table_array, col_index_num, ) What it means: =VLOOKUP(thi...