Excel TEXT function Examples

The following worksheets have various TEXT function examples as mentioned in the following Support.Office.c

TEXT function reference

Each worksheet is listed below, along with what kind of examples you'll find, and each sheet name is hyperlinke

Worksheet
Format Codes
TEXT() function intro
Thousands separator
Number, currency, accounting
Months, days, years
Hours, minutes, seconds
Date & time
Percentage
Fraction
Scientific notation
Special
Symbols
Combine multiple formats
Custom
Leading 0's
New line
Page Header & Footer
unction Examples
have various TEXT function examples as mentioned in the following Support.Office.com article:

elow, along with what kind of examples you'll find, and each sheet name is hyperlinked to the sheet.

Description
List of the format codes from the Format Cells dialog
TEXT function overview and examples
Using the Thousands separator with the TEXT function
Applying number, currency and accounting formats with the TEXT function
Applying months, days and year formats with the TEXT function
Applying hours, minutes and second formats with the TEXT function
Applying date & and time formats with the TEXT function
Applying percentage formats with the TEXT function
Applying fraction formats with the TEXT function
Applying Scientific notation formats with the TEXT function
Applying Special formats with the TEXT function
Using symbols and custom formats with the TEXT function
Combining multiple text and value formats with the TEXT function
Creating and applying Custom formats with the TEXT function
Applying leading 0's with the TEXT function and convert text to numbers
Applying new lines with CHAR(10) and the TEXT function
Adding dates and times to a Page Header or Footer
Excel Format Codes from the Format Cells dialog
Format Code Description
General No specific number format
Number General number display with or without thousand separators and decimals
Currency Currency formats are used for general monetary values
Accounting Accounting formats line up the currency symbols and decimal points in a column
Date Date formats display date and time serial numbers as date values. Date formats that
begin with an asterisk (*) respond to changes in regional date and time settings that are
specified for the operating system. Formats without an asterisk are not affected by
operating system settings.
Time Time formats display date and time serial numbers as date values. Time formats that
begin with an asterisk (*) respond to changes in regional date and time settings that are
specific for the operating system. Formats without an asterisk are not affected by
operating system settings.
Percentage Percentage formats multiply the cell value by 100 and displays the results with a percent
symbol (%).
Fraction Fraction formats display numbers as fractions rather than decimals.
Scientific The Scientific format displays a number in exponential notation, replacing part of the
number with E+ n, where E (which stands for Exponent) multiplies the preceding
number by 10 to the n th power. For example, a 2-decimal Scientific format displays
12345678901 as 1.23E+10, which is 1.23 times 10 to the 10th power.

Text Text format cells are treated as text even when a number is in the cell.
The cell is displayed exactly as entered.
Special Special formats are useful for tracking list and database values. The following special
formats are included:
• Zip Code
• Zip Code + 4
• Phone Number
• Social Security Number

Custom Create your own format code, using one of the existing codes as a starting point

See more online: TEXT function reference

 Example These are the format codes you'll see in • Use Ctrl+1 to launch the Format Ce
the Format Cells dialog. You can apply
1234.56 the format of your choice, then click the
1,234.56 Custom category, and copy the format
that's displayed in the Type box into your
$1,234.56 TEXT formula.
$ 1,234.56
=TEXT(C4,"$#,###0.00")
10/13/2019
Just make sure that the format code has
quotes around it ("format code"), or
you'll get an error.

12:16:56 PM

12.30%

1 3/4
1.23E+08

1234

12345
12345-1234
(123) 456-7899
123-45-6789

N/A
1 to launch the Format Cells dialog
TEXT function
• The TEXT function lets you convert a number into a text string.

• =TEXT(Value you want to format, “Format you want to apply")

Basic examples
Value Formula Result
10/13/2019 =TEXT(A9,"MM/DD/YY") 10/13/19
10/13/2019 =TEXT(A10,"DDDD") Sunday
0.285 =TEXT(A11,"0.00%") 029%

Combining text and numbers without the TEXT function

Text to combine Value Formula
Report Printed on: 03/14/12 =A15&" "&B15
Package Delivered at: 3:30 PM =A16&" "&B16
Weekly Revenue: $66,348.72 =A17&" "&B17

Combining text and numbers with the TEXT function

Text to combine Value Formula
Report Printed on: 03/14/12 =A21&" "&TEXT(B21,"mm/dd/yy")
Package Delivered at: 3:30 PM =A22&" "&TEXT(B22,"HH:MM AM/PM")
Weekly Revenue: $66,348.72 =A23&" "&TEXT(B23,"$#,###.##")

See more online: TEXT function reference

 Note that we use cell references, like
=TEXT(A9,...), instead of putting our text values
directly into our formulas. It's much easier to
change them if they're out in the open.

Result
Report Printed on: 40982
Package Delivered at: 0,645833333333333
Weekly Revenue: 66348,72

Result
Report Printed on: 03/14/12 Use the TEXT function to force Excel to
Package Delivered at: 03:30 p.m. use the number format that you want.
Weekly Revenue: $66348,72
Excel to
u want.
Thousands separator
Value Formula Result • Excel separates thousands by commas if the for
comma (,) that is enclosed by number signs (#) or
12200000 =TEXT(A4,"#,###") 12200000,
12200000 =TEXT(A5,"0,000.00") 12200000,00000 • A comma that follows a digit placeholder scales
1,000. For example, if the format_text argument
12200000 =TEXT(A6,"#,") 12200000, displays the number 12,200,000 as 12,200.0.
12200000 =TEXT(A7,"#,###.0,") 12200000,0,
12200000 =TEXT(A8,"0.0,,") 12.200.000,,

See more online: TEXT function reference

ates thousands by commas if the format contains a
at is enclosed by number signs (#) or by zeros.

hat follows a digit placeholder scales the number by

ample, if the format_text argument is "#,###.0,", Excel
number 12,200,000 as 12,200.0.
Number, Currency and Accounting formats
Value Description Formula
1234.56 Number - General =TEXT(A4,"0.00")
1234.56 Number - thousands separator, =TEXT(A5,"#,##0")
1234.56 no decimals
Number - thousands separator, =TEXT(A6,"#,##0.00")
2 decimals- no decimals
1234.56 Currency =TEXT(A7,"$#,##0")
1234.56 Currency - 2 decimals =TEXT(A8,"$#,##0.00")
-1234.56 Currency - 2 decimals, =TEXT(A9,"$#,##0.00_);($#,##0.00)")
1234.56 negative value
Accounting - no decimals =TEXT(A10,"$ * #,##0")
1234.56 Accounting - 2 decimals =TEXT(A11,"$ * #,##0.00")

Cell formatting with negative value in red - the TEXT function doesn't support color formatting
($1,235) Currency with a negative value formated as $#,##0._);[Red]($#,##0.) from the Format Cells dialog

See more online: TEXT function reference

 Result NOTES: not all formats copied from the Format Cells dialog will behave with
the TEXT function the same way as a cell with the same value formatted on its
1.235 own.
1234,560
• The TEXT function converts numeric values to text, so Excel no longer sees
1234,56000 the value as a number - Notice how the Result values are all left-aligned, but
$1234,560 the starting values in column A are all right-aligned.
$1234,56000 • Currency format with [Red] will color a negative value red when a cell is
($1234,56000) formatted, but the TEXT function doesn't support text color.
$ 1234,560 • Some accounting formats will be rejected in the TEXT function. For example,
$ 1234,56000 the following format will result in a #VALUE! error:

=TEXT(A11,"_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)")

formatting
With the Accounting format you'll need to experiment until it displays the way
mat Cells dialog that you want.
• Currency symbol selection
Date Formats - Months, days and years

Date: 10/13/2019

To display As Format Formula Result In this case we're

Months 1–12 "m" =TEXT(B3,"m") 10 • =TEXT(B3,"M")
Months 01–12 "mm" =TEXT(B3,"mm") 10
But you could en
Months Jan–Dec "mmm" =TEXT(B3,"mmm") Oct you surround it in
Months January–December "mmmm" =TEXT(B3,"mmmm") October
• =TEXT("3/12/14
Months J–D "mmmmm" =TEXT(B3,"mmmmm") O
Days 1–31 "d" =TEXT(B3,"d") 13 It's much better t
much easier to se
Days 01–31 "dd" =TEXT(B3,"dd") 13
Days Sun–Sat "ddd" =TEXT(B3,"ddd") Sun
Days Sunday–Saturday "dddd" =TEXT(B3,"dddd") Sunday
Years 00–99 "yy" =TEXT(B3,"yy") 19
Years 1900–9999 "yyyy" =TEXT(B3,"yyyy") 2019

You could also use a Named Range instead of a cell value, where cell B3 has been named "StartDate".

Formula Result
=TEXT(StartDate,"m") 10

See more: Define and use names in formulas

See more online: TEXT function reference

 In this case we're referencing the date in cell B3 like this:

• =TEXT(B3,"M")

But you could enter the date directly in the formula as long as
you surround it in quotes, like:

• =TEXT("3/12/14","m")

It's much better to reference cell values though, as they're

much easier to see and change.

amed "StartDate".
Time formats - Hours, minutes and seconds
Current time: 12:16 PM

To display As Format Formula Result

Hours 0-23 "h" =TEXT(B3,"h") 12
Hours 00-23 "hh" =TEXT(B3,"hh") 12
Minutes 0-59 "m" =TEXT(B3,"m") 10
Minutes 00-59 "mm" =TEXT(B3,"mm") 10
Seconds 0-59 "s" =TEXT(B3,"s") 56
Seconds 00-59 "ss" =TEXT(B3,"ss") 56
Time 12 PM "h AM/PM" =TEXT(B3,"h AM/PM") 12 PM
Time 12:16 PM "h:mm AM/PM" =TEXT(B3,"h:mm AM/PM") 12:16 PM
Time 12:16:56 p "h:mm:ss A/P" =TEXT(B3,"h:mm:ss A/P") 12:16:56 p
Time 12:16:56.28 "h:mm:ss.00" =TEXT(B3,"h:mm:ss.00") 12:16:56.28
Elapsed Time
1:02 "[h]:mm" =TEXT(B16,"[h]:mm") 1:02
(hours & minutes)
Elapsed Time
62:16 "[mm]:ss" =TEXT(B17,"[mm]:ss") 62:16
(minutes & seconds)

Elapsed Time
3735.80 "[ss].00" =TEXT(B18,"[ss].00") 3735.80
(seconds & hundredths)

See more online: TEXT function reference

12-hour clock
• AM/PM, am/pm, A/P, a/p - Displays the hour based on a 12-
hour clock. Excel displays AM, am, A, or a for times from
midnight until noon and PM, pm, P, or p for times from noon
until midnight.

24-hour clock
• If you leave off the AM/PM, Excel will display the time based
on a 24-hour clock, like 17:30.
Combine Date & Time formats
Current Date/Time: 10/13/2019 12:16 PM

Formula Result
="Date: "&TEXT(B3,"mm/dd/yyyy") Date: 10/13/2019
="Date-time: " & TEXT(B3, "m/d/yyyy h:mm AM/PM") Date-time: 10/13/2019 12:16 PM

Full sentence
Today is Sunday, October 2019, and the current time is 12/16 PM.

="Today is "&TEXT(B3,"dddd, mmmm yyyy")&", and the current time is "&TEXT(B3,"hh/mm AM/PM")&"."

See more online: TEXT function reference

 Combine Dates & Times – You can easily combine date
and time format strings, like:

=TEXT(B3 "m/d/yyyy h:mm AM/PM")

You're not limited to putting text in front of the TEXT function,
you can also put it afterwards like in the following example:

="Today is "&TEXT(B3,"dddd, mmmm yyyy")&", and the

AM/PM")&"." current time is "&TEXT(B3,"hh/mm AM/PM")&"."
Percentage formats
Value Description Formula Result Percentages:
0.2447400884 No decimals =TEXT(A4,"0%") 24% Percentage formats will display a decimal in
0.2447400884 1 decimal =TEXT(A5,"0.0%") 24% according to the number of decimal places
rounded to 1 decimal place.
0.2447400884 2 decimals =TEXT(A6,"0.00%") 024%

See more online: TEXT function reference

s:
ormats will display a decimal in its equivalent % format and round
the number of decimal places entered. So 24.5% has been
1 decimal place.
Fraction formats
Value Description Formula Result Fractions:
4.34 Up to one digit (1/4) =TEXT(A4,"# ?/?") 4 1/3 • After you apply a fr
0.34 Up to one digit (1/4) ** =TRIM(TEXT(A5,"# ?/?")) 1/3 actual fractions that
4.34 Up to two digits (21/25) =TEXT(A6,"# ??/??") 4 17/50 For
example, typing .5 o
4.34 Up to three digits (312/943) =TEXT(A7,"# ???/???") 4 17/50 formatted with a frac
4.34 As halves (1/2) =TEXT(A8,"# ?/2") 4 1/2
• If no fraction forma
4.34 As quarters (2/4) =TEXT(A9,"# ?/4") 4 1/4 as 1/2, it will be form
4.34 As sixteenths (8/16) =TEXT(A10,"# ??/16") 4 5/16 Fraction format, and
4.34 As tenths (3/10) =TEXT(A11,"# ?/10") 4 3/10
** Note the second ex
4.34 As hundreths (30/100) =TEXT(A12,"# ??/100") 4 34/100 from decimal only valu

See more online: TEXT function reference

Fractions:
• After you apply a fraction format to a cell, decimal numbers as well as
actual fractions that you type in that cell will be displayed as a fraction.
For
example, typing .5 or 1/2 results in 1/2 when the cell has been
formatted with a fraction type of Up to one digit (1/4).
• If no fraction format is applied to a cell, and you type a fraction such
as 1/2, it will be formatted as a date. To display it as a fraction, apply a
Fraction format, and then retype the fraction.
** Note the second example uses the TRIM function to trim the leading space
from decimal only values.
Scientific notation formats
Value Description Formula Result Scientific notation:
12,200,000 Scientific - 7 places =TEXT(A4,"0.00E+00") 1.220E+04 E (E-, E+, e-, e+) - Displays a n
12,200,000 Scientific - 6 places =TEXT(A5,"#0.0E+0") 1.220E+4 Excel displays a number to th
to the number of places that
example, if the format_text a
number 12,200,000 as 1.22E
to "#0.0E+0", Excel displays 12.

See more online: TEXT function reference

Scientific notation:
E (E-, E+, e-, e+) - Displays a number in scientific (exponential) format.
Excel displays a number to the right of the "E" or "e" that corresponds
to the number of places that the decimal point was moved. For
example, if the format_text argument is "0.00E+00", Excel displays the
number 12,200,000 as 1.22E+07. If you change the format_text argument
to "#0.0E+0", Excel displays 12.2E+6.
Special formats - Zip code, Zip +4, Phone number, Social Secu
Value Description Formula
12345 Zip Code =TEXT(A4,"00000")
123456789 Zip Code + 4 =TEXT(A5,"00000-0000")
1234567899 Phone Number =TEXT(A6,"[<=9999999]###-####;(###) ###-####")
123456789 Social Security Number =TEXT(A7,"000-00-0000")

See more online: TEXT function reference

ber, Social Security number
Result You can create your own Special fomats with a Custom number
format. For example, a standard 16-digit credit card format
12345 could be:
12345-6789
"####-####-####-####"
(123) 456-7899
123-45-6789
Using Symbols with the TEXT function to create custom format

Use this key

Symbol Name To enter
combination
$ Dollar sign ¢ ALT+0162
+ Plus sign £ ALT+0163
( Left parenthesis ¥ ALT+0165
: Colon € ALT+0128
^ Circumflex accent (caret)
' Apostrophe Symbols are displayed exactly as entered. For example,
{ Left curly bracket
=TEXT(A4,"~$#,###") would display "~$1,235".
< Less-than sign
= Equal sign
- Minus sign
/ Slash mark Use the Custom number format dialog to help build your
) Right parenthesis own custom number formats. It's much easier to modify
an existing format than try to build your own from
! Exclamation point scratch!
& Ampersand
~ Tilde
} Right curly bracket
> Greater-than sign
Space character

See more: Create or delete a custom number format

See more online: TEXT function reference

n to create custom formats

actly as entered. For example,

ld display "~$1,235".

ormat dialog to help build your

ats. It's much easier to modify
y to build your own from
Create sentences with the TEXT function
Formula Result
=D4&" sold "&TEXT(E4,"$#,###")&" worth of units." Robbie Burke sold $2800, worth of units.
=D4&" had "&TEXT(F4, "0%")&" of total sales." Robbie Burke had 40% of total sales.

You can combine multiple values and text with the Ampersand (&) and
punctuation separators, like &", "& to add a comma followed by a
space. This is called "concatenation".

See more online: TEXT function reference

Details
Salesperson Sales % of Total
Robbie Burke $2,800.00 40%
Custom formats
Value Description Formula
123456 ID # & 9-Digit number ="ID# "&TEXT(A4,"000000000")
123456 Latitude/Longitude =TEXT(A5,"###° 00' 00''")

See more online: TEXT function reference

Result You can create your own Special formats with a Custom number format.
ID# 000123456
12° 34' 56''
tom number format.
Restore leading 0's and convert back to numbers
The TEXT function converts numeric values to TEXT
can't perform mathematical operations on them. Y
Original Leading 0's TEXT Convert back use the double-unary (--) operator to convert text v
Value removed function to Numbers back to numbers, like:
00001 1 00001 1 =--D4
00012 12 00012 12
Which will convert 00001 back to 1
00123 123 00123 123
01234 1234 01234 1234 If you need to use a TEXT converted number in a fo
try to use it before using (--), like =D4+2, which will
12345 12345 12345 12345 3. If Excel returns an error then you can use =--D4+

See more online: TEXT function reference

converts numeric values to TEXT, so you
ematical operations on them. You can
ry (--) operator to convert text values
ke:

00001 back to 1

a TEXT converted number in a formula

using (--), like =D4+2, which will return
n error then you can use =--D4+2.
Use CHAR(10) with the TEXT function to add a new line

TEXT w/a
Formula Line Break
Today is:
="Today is: "&CHAR(10)&TEXT(TODAY(),"mm/dd/yy")
10/13/19

You can use CHAR(10) with the TEXT function to create a new line, but
you need to format the cell to Wrap Text.

Format Cells (Ctrl+1) > Alignment > Check the Wrap Text check box

See more online: TEXT function reference

ew line
Report Time: 12:16:56 Report Date: 10/13/2019

Add a Date/Time Stamp to a Page Header/Footer

Page Header/Footer
You don't need to use the TEXT function to add a Page Header or Footer.

1. To add a Date/Time stamp to a Page Header/Footer first click the Page Setup flyout in the
Ribbon: Pasgfsadfage Layout > Page Setup > Flyout.

2. In the Page Setup dialog click the Header/Footer tab.

3. Add your text in the Left/Center/Right section(s), then click the Date or Time buttons.

1. Choose the Page Setup flyout in the Page Layout ribbon tab

2. Click the Header/Footer tab on the Page Setup dialog

Report Time: 12:16:56 Report Date: 10/13/2019

See more online: TEXT function reference

Report Time: 12:16:56 Report Date: 10/13/2019

3. Add your section text and click on the Date or Time buttons above

Date/Time
selections
Report Time: 12:16:56 Report Date: 10/13/2019