Excel TEXT function Examples

The following worksheets have various TEXT function examples as mentioned in the followi

TEXT function reference

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

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
ction Examples
s have various TEXT function examples as mentioned in the following Support.Office.com article:

below, 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
m article:

ed to the sheet.
Excel Format Codes from the Format Cells dialog

Format Code
General
Number
Currency
Accounting
Date

Time

Percentage

Fraction
Scientific

Text

Special

Custom

See more online: TEXT function reference

 l Format Codes from the Format Cells dialog

Description
No specific number format
General number display with or without thousand separators and decimals
Currency formats are used for general monetary values
Accounting formats line up the currency symbols and decimal points in a column
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 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 formats multiply the cell value by 100 and displays the results with a
percent symbol (%).
Fraction formats display numbers as fractions rather than decimals.
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 format cells are treated as text even when a number is in the cell.
The cell is displayed exactly as entered.
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

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

more online: TEXT function reference

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

09:53:55 AM

12.30%

1 3/4
1.23E+08

1234

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

N/A
Use Ctrl+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
9/7/2024 =TEXT(A9,"MM/DD/YY") 09/07/24
9/7/2024 =TEXT(A10,"DDDD") Saturday
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 use
Package Delivered at: HH:MM ÖS the number format that you want.
Weekly Revenue: $66348,72
el to use
Thousands separator

Value Formula Result • Excel separates thousands by commas i

(,) 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 placeholde
For example, if the format_text argumen
12200000 =TEXT(A6,"#,") 12200000, 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

es thousands by commas if the format contains a comma
ed by number signs (#) or by zeros.

follows a digit placeholder scales the number by 1,000.

the format_text argument is "#,###.0,", Excel displays
200,000 as 12,200.0.
Number, Currency and Accounting formats

Value Description
1234.56 Number - General
1234.56 Number - thousands separator,
no decimals
1234.56 Number - thousands separator,
1234.56 2 decimals
Currency - no decimals
1234.56 Currency - 2 decimals
-1234.56 Currency - 2 decimals,
1234.56 negative value
Accounting - no decimals
1234.56 Accounting - 2 decimals

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

See more online: TEXT function reference

ounting formats

Formula Result NOTES: not all formats copied from the Format Ce
function the same way as a cell with the same val
=TEXT(A4,"0.00") 1.235
=TEXT(A5,"#,##0") 1234,560 • The TEXT function converts numeric values to te
value as a number - Notice how the Result values
=TEXT(A6,"#,##0.00") 1234,56000 values in column A are all right-aligned.
=TEXT(A7,"$#,##0") $1234,560
• Currency format with [Red] will color a negative
=TEXT(A8,"$#,##0.00") $1234,56000 but the TEXT function doesn't support text color.
=TEXT(A9,"$#,##0.00_);($#,##0.00)") ($1234,56000)
• Some accounting formats will be rejected in the
=TEXT(A10,"$ * #,##0") $ 1234,560 following format will result in a #VALUE! error:
=TEXT(A11,"$ * #,##0.00") $ 1234,56000
=TEXT(A11,"_($* #,##0.00_);_($* (#,##0.00);_($*

With the Accounting format you'll need to experim

ed - the TEXT function doesn't support color formatting you want.
mated as $#,##0._);[Red]($#,##0.) from the Format Cells dialog
 • Currency symbol selection
ormats copied from the Format Cells dialog will behave with the TEXT
me way as a cell with the same value formatted on its own.

tion converts numeric values to text, so Excel no longer sees the

er - Notice how the Result values are all left-aligned, but the starting
n A are all right-aligned.

at with [Red] will color a negative value red when a cell is formatted,
ction doesn't support text color.

ng formats will be rejected in the TEXT function. For example, the

will result in a #VALUE! error:
* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)")

ting format you'll need to experiment until it displays the way that
Date Formats - Months, days and years

Date: 9/7/2024

To display As Format Formula Result

Months 1–12 "m" =TEXT(B3,"m") 9
Months 01–12 "mm" =TEXT(B3,"mm") 09
Months Jan–Dec "mmm" =TEXT(B3,"mmm") Sep
Months January–December "mmmm" =TEXT(B3,"mmmm") September
Months J–D "mmmmm" =TEXT(B3,"mmmmm") S
Days 1–31 "d" =TEXT(B3,"d") 7
Days 01–31 "dd" =TEXT(B3,"dd") 07
Days Sun–Sat "ddd" =TEXT(B3,"ddd") Sat
Days Sunday–Saturday "dddd" =TEXT(B3,"dddd") Saturday
Years 00–99 "yy" =TEXT(B3,"yy") 24
Years 1900–9999 "yyyy" =TEXT(B3,"yyyy") 2024

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

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

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.

ll B3 has been named "StartDate".

Time formats - Hours, minutes and seconds
Current time: 9:53 AM

To display As Format Formula

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

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

See more online: TEXT function reference

Result
9 12-hour clock
09 • 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
9 noon and PM, pm, P, or p for times from noon until midnight.
09
24-hour clock
55 • If you leave off the AM/PM, Excel will display the time based on a
55 24-hour clock, like 17:30.
9 AM
9:53 AM
9:53:55 a
9:53:55.13

1:02

62:16

Err:502
Combine Date & Time formats
Current Date/Time:

Formula
="Date: "&TEXT(B3,"mm/dd/yyyy")
="Date-time: " & TEXT(B3, "m/d/yyyy h:mm AM/PM")

Full sentence
Today is Saturday, September 2024, and the current time is 09/53 AM.

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

See more online: TEXT function reference

 9/7/2024 9:53 AM

Result
Date: 09/07/2024
Date-time: 9/7/2024 9:53 AM 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
time is 09/53 AM. can also put it afterwards like in the following example:

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

the current time is "&TEXT(B3,"hh/mm AM/PM")&"." time is "&TEXT(B3,"hh/mm AM/PM")&"."
sily combine date and

of the TEXT function, you

wing example:

y")&", and the current

"
Percentage formats

Value Description Formula Result Percentages:

0.244740088393 No decimals =TEXT(A4,"0%") 24% Percentage formats will display a d
0.244740088393 1 decimal =TEXT(A5,"0.0%") 24% according to the number of decim
1 decimal place.
0.244740088393 2 decimals =TEXT(A6,"0.00%") 024%

See more online: TEXT function reference

ercentages:
ercentage formats will display a decimal in its equivalent % format and round
ccording to the number of decimal places entered. So 24.5% has been rounded to
decimal place.
Fraction formats

Value Description Formula Result Fractions:

4.34 Up to one digit (1/4) =TEXT(A4,"# ?/?") 4 1/3 • After you
0.34 Up to one digit (1/4) ** =TRIM(TEXT(A5,"# ?/?"))1/3 actual frac
4.34 Up to two digits (21/25) =TEXT(A6,"# ??/??") 4 17/50 example, t
with a frac
4.34 Up to three digits (312/943) =TEXT(A7,"# ???/???") 4 17/50
4.34 As halves (1/2) =TEXT(A8,"# ?/2") 4 1/2 • If no frac
1/2, it will
4.34 As quarters (2/4) =TEXT(A9,"# ?/4") 4 1/4 format, an
4.34 As sixteenths (8/16) =TEXT(A10,"# ??/16") 4 5/16
** Note the
4.34 As tenths (3/10) =TEXT(A11,"# ?/10") 4 3/10 decimal on
4.34 As hundreths (30/100) =TEXT(A12,"# ??/100") 4 34/100

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+) - D
12,200,000 Scientific - 6 places =TEXT(A5,"#0.0E+0") 1.220E+4 displays a number
number of places t
format_text argum
as 1.22E+07. If you
12.2E+6.

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
12345 Zip Code
123456789 Zip Code + 4
1234567899 Phone Number
123456789 Social Security Number

See more online: TEXT function reference

Zip code, Zip +4, Phone number, Social Security number

Formula Result You can create your ow

format. For example, a
=TEXT(A4,"00000") 12345 be:
=TEXT(A5,"00000-0000") 12345-6789
"####-####-####-####
=TEXT(A6,"[<=9999999]###-####;(###) ###-####
(123) 456-7899
=TEXT(A7,"000-00-0000") 123-45-6789

ion reference
You can create your own Special fomats with a Custom number
format. For example, a standard 16-digit credit card format could
be:

"####-####-####-####"
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 own
) Right parenthesis custom number formats. It's much easier to modify an existing
! Exclamation point format than try to build your own from 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

reate custom formats

as entered. For example,

splay "~$1,235".

at dialog to help build your own

uch easier to modify an existing
wn from scratch!
Create sentences with the TEXT function

Formula
=D4&" sold "&TEXT(E4,"$#,###")&" worth of units.
=D4&" had "&TEXT(F4, "0%")&" 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

e TEXT function
Details
Result Salesperson Sales
Robbie Burke sold $2800, worth of units. Robbie Burke $2,800.00
Robbie Burke had 40% of total sales.

xt with the Ampersand (&) and

add a comma followed by a space.
% of Total
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''
number format.
Restore leading 0's and convert back to numbers
The TEXT function converts numeric value
can't perform mathematical operations on
Original Leading 0's TEXT Convert back the double-unary (--) operator to convert
Value removed function to Numbers 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 numb
to use it before using (--), like =D4+2, whic
12345 12345 12345 12345 Excel returns an error then you can use =-

See more online: TEXT function reference

T function converts numeric values to TEXT, so you
rform mathematical operations on them. You can use
ble-unary (--) operator to convert text values back to
s, like:

will convert 00001 back to 1

eed to use a TEXT converted number in a formula try

before using (--), like =D4+2, which will return 3. If
urns an 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: "&CHAR(10)&TEXT(TODAY(),"mm/dd/yy") Today is:

09/07/24

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

new line
Report Time: 09:53:55 Report Date: 09/07/2024

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: 09:53:55 Report Date: 09/07/2024

See more online: TEXT function reference

 Report Time: 09:53:55 Report Date: 09/07/2024

he Ribbon:

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

Date/Time
selections
Report Time: 09:53:55 Report Date: 09/07/2024
 Report Time: 09:53:55 Report Date: 09/07/2024

me buttons above

Date/Time
selections