Pinterest Pixel

Excel´s TEXT Function

  What does it do? Converts a numeric value to text and lets you specify the display... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Excel´s TEXT Function | MyExcelOnline Excel´s TEXT Function | MyExcelOnline

 

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:

Excel´s TEXT Function

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

Excel´s TEXT Function | MyExcelOnline

Download excel workbookText.xlsx

How to Use the Text Formula in Excel

 

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”
See also  Cleaning Data with Excel's REPLACE Formula

 

If you like this Excel tip, please share it
Excel´s TEXT Function | MyExcelOnline Excel´s TEXT Function | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...