How to use the TEXT function

Author: Oscar Cronquist Article last updated on October 01, 2021

This article demonstrates how to use the TEXT function in great detail. The formula in cell D3 formats the number in cell B3 based on the formatting text in cell C3 and returns the result to cell D3.

Formula in cell D3:

=TEXT(B3,C3)

The formatting text lets you format numbers as you prefer, 0,000 formats numbers by inserting a comma after every third digit. You have a plethora of formatting characters you can use all described below.

Excel function syntax

TEXT(value, format_text)

Converts a value to text in a specific number format.

Arguments

value	The string you want to format. You can use a cell reference here or use a text string.
format_text	Formatting code allowing you to change the way, for example, a date or a number is displayed to the Excel user.

How to create your own custom formatting - TEXT function
Formatting codes you can use in the TEXT function
Number formatting
How to hide specific values - TEXT function
Custom groups
Fractions
Phone numbers
Indents
Singular / Plural
Get Excel file

1. How to create your own custom formatting - TEXT function

The TEXT function lets you easily create your own custom formats, there are four parts shown in the picture below.

You can use the semicolon to separate different settings for different values, the zero is a character symbol (among others) for digits and the @ is a character symbol for text values.

Positive values
Negative values
Zero values
Text values

The format_ text argument is 0;-0;0;@ Positive numbers are returned positive, no change.

Formula in cell D3:

=TEXT(B3, C3)

The format_text argument is specified in cell C3 in this example, however, you can type it in the second argument as well. Like this:

=TEXT(B3,"0;-0;0;@")

You can ignore the semicolons if you want to format all values equally.

1.1 How to convert negative numbers to positive numbers?

TEXT function remove sign from negative values

The image above shows you a negative number in cell B4, the formula in cell C4 removes the sign.

The second container lets you format negative numbers, this means that you can use the TEXT function to remove the minus sign from negative numbers.

=TEXT(B3,"0;0;0;@")

The bolded zero in the formula above shows the container for negative numbers. The minus sign is removed, negative numbers are converted to positive numbers.

1.2 How to convert positive numbers to negative numbers?

TEXT function convert positive numbers to negative numbers

The picture above demonstrates a formula in cell C4 that converts positive numbers to negative numbers.

=TEXT(B3,"-0;-0;0;@")

The first container in the formula above (bolded) lets you format positive numbers, the minus sign formats positive numbers as negative numbers.

1.3 How to change positive numbers to negative numbers and vice versa?

TEXT function change signs for numbers

The image above shows the TEXT function in cell C4, it changes positive numbers to negative and negative numbers to positive.

=TEXT(B3,"-0;0;0;@")

The first and second container (bolded) formats numbers as explained above. You can also multiply the number with -1, like this:

=B3*-1

This is a lot easier than using the TEXT function. The asterisk multiples the number in cell B3 with -1 which changes the sign, in other words, positive numbers become negative and vice versa.

2. Formatting codes you can use

Note that you cant perform arithmetic operations to a value the TEXDT function has calculated. Excel handles these values as text values even if all value characters are digits.

2.1 How to insert 0 zeros to the right of the decimal point

The decimal point lets you round numbers, it also inserts additional decimals even if it is not needed.

Formula in cell D9:

=TEXT(B9,C9)

The format_text argument is 0.00. This adds additional zeros to the right of the decimal point, even if they are not needed.

For example, 6 becomes 6.00. I will show you below how to round numbers, follow the link to get to the section below.

2.2 How to separate digits by a comma

The comma lets you separate numbers into any number of digits. The example above shows how to separate every three digits with a comma. This makes large numbers easier to read.

Formula in cell D3:

=TEXT(B3, C3)

The format_text argument is "0,000", this separates text with a comma every third digit.

2.3 How to add 0 (zeros) to the right and left of the decimal point

The image above shows different formatting patterns using the 0 (zero) character. It allows you to add 0 (zeros) before and after the decimal point.

Formula in cell D3:

=TEXT(B3, C3)

The format_text argument is "000", this returns a number with leading zeros if the number is smaller than 100.

How to type multiple zeros in one cell?
This is not working for me, Excel shortens the digits to only a single 0 (zero).Type the zeros like this: '000
Then press Enter.

2.4 Hashtag character lets you hide extra zeros

The image above shows how to use hashtags in the TEXt function. The hashtag is a digit symbol and 0's (zeros) to the left of the decimal point is not shown.

Formula in cell D3:

=TEXT(B3, C3)

2.5 How to align numbers by the decimal point

The image above shows what happens if you use the ? (question mark) as a placeholder in the TEXT function. It aligns numbers based on the decimal point and hides 0's (zeros) if possible.

Formula in cell D3:

=TEXT(B3, "???")

You can also use the ? (question mark) to create fractions based on a decimal number.

2.6 How to create a percentage

The picture above demonstrates how to format numbers as percentages.

Formula in cell D3:

=TEXT(B3, "0%")

The decimal point and the 0 (zero) lets you format the percentage number.

2.7 How to format a number using the Scientific notation

The image shows how to format numbers using the scientific format. It allows you to show very large numbers (many digits) or very small numbers in a decimal form.

Formula in cell D3:

=TEXT(B3, "0.0E+0")

E+ E- e+ e- - Scientific format.

E or e represents "value times ten raised to the power of". Superscripted exponents can be shown in Excel but not as a result of a formula. As far as I know, you need to format the output to create a superscripted exponent.

Number	Scientific format	x^n
2	2E+0	2*10^0
200	2E+2	2*10^2
0.2	2E-1	2*10^-1
0.02	2E-2	2*10^-2
0.002	2E-3	2*10^-3

2.8 How to use text in the TEXT function - @ At character

The image above shows how to use the "at" character with text values in the TEXT function.

Formula in cell D3:

=TEXT(B3, "@ car")

The at character is a placeholder for text values, in this case, the text value in cell B3. @ = "one", "@ car" becomes "one car" in cell D3.

@ - Text characters

2.9 How to work with text and numbers in the TEXT function

TEXT function text and numbers

Formula in cell D3:

=TEXT(B3, 0 " cars")

TEXT function text and numbers1

"Text" - Characters enclosed by quotation marks show the characters and digits.

2.10 Date formats

The image above demonstrates how to work with dates using the TEXT function.

Placeholder	Source date	Result	Description
m	1-13-2017	1	Extracts month as a number without a leading zero.
mm	1-13-2017	01	Extracts month as a number with a leading zero.
mmm	1-13-2017	Jan	Extracts month as a three-letter abbreviation.
mmmm	1-13-2017	January	Extracts full month name.
d	11-3-2017	3	Extracts day as a number without a leading zero.
dd	11-3-2017	03	Extracts day as a number with a leading zero.
ddd	11-13-2017	Mon	Extracts weekday name as a three-letter abbreviation.
dddd	11-13-2017	Monday	Extracts full weekday name.
yy	11-3-2017	17	Extracts year as a two-digit number.
yyyy	11-3-2017	2017	Extracts full year.

2.10.1 How to extract the month number without a leading zero?

TEXT function month without leading zero

The image above shows how to extract the month number without a leading zero using the TEXT function, the source date is in cell B3.

1 - January
2 - February
3 - March
4 - April
5 - May
6 - June
7 - July
8 - August
9 - September
10 - October
11 - November
12- December

Formula in cell C3:

=TEXT(B3, "m")

m - Month number without a leading zero

2.10.2 How to extract the month number with a leading zero?

TEXT function month with leading zero

The image above shows how to extract the month number with a leading zero using the TEXT function, the source date is in cell B3.

01 - January
02 - February
03 - March
04 - April
05 - May
06 - June
07 - July
08 - August
09 - September
10 - October
11 - November
12- December

Formula in cell C3:

=TEXT(B3, "mm")

mm - Month number with a leading zero

2.10.3 How to extract the month as a three-letter abbreviation?

mmm - Month as an abbreviation, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov and Dec.

2.10.4 How to extract the full month from a date?

mmmm - Full month name.

2.10.5 How to extract the day as a number without a leading zero from a date?

d- Day as a number without a leading zero

2.10.6 How to extract the day as a number with a leading zero from a date?

dd - Day as a number with a leading zero when necessary

2.10.6 How to extract the weekday as a three-letter abbreviation from a date?

ddd - Day as an abbreviation, Sun, Mon, Tue, Wed, Thu, Fri and Sat.

2.10.6 How to extract the full weekday name from a date?

dddd - Full day name.

2.10.6 How to extract the year as a two-digit number from a date?

yy - Year as a two-digit number.

2.10.6 How to extract the full year from a date?

yyyy - Year as a four-digit number.

2.11 Time formats

h - Hour as a digit without a leading zero.

[h] - Shows more than 24 hours.

hh - Hour as a digit with a leading zero when needed.

m - Minute without a leading zero.

[m] - Shows more than 60 minutes.

mm - Minute with a leading zero when necessary.

s - Second without a leading zero.

[s] - Shows more than 60 seconds.

ss - Seconds with a leading zero when necessary.

AM/PM - Shows the time of 12-hour hours.

am/pm - See above.

A/P - See above.

a/p - See above.

The image above demonstrates how to work with time values using the TEXT function.

Placeholder	Source date	Result	Description
h	9:15:04 AM	9	Hour as a digit without a leading zero.
[h]	1-13-2017	01	Extracts month as a number with a leading zero.
hh	1-13-2017	Jan	Extracts month as a three-letter abbreviation.
m	1-13-2017	January	Extracts full month name.
[m]	11-3-2017	3	Extracts day as a number without a leading zero.
mm	11-3-2017	03	Extracts day as a number with a leading zero.
s	11-13-2017	Mon	Extracts weekday name as a three-letter abbreviation.
[s]	11-13-2017	Monday	Extracts full weekday name.
ss	11-3-2017	17	Extracts year as a two-digit number.
AM/PM	11-3-2017	2017	Shows the time of 12-hour hours.
am/pm	11-3-2017	2017	See above.
A/P	11-3-2017	2017	See above.
a/p	11-3-2017	2017	See above.

2.12 Why isn't the TEXT function working?

TEXT function escape characters

The TEXT function returns a #VALUE! error if you have a placeholder character in the second argument, see example formula in cells C3 and C4 above. Use the backslash to escape placeholder characters.

Formula in cell D3:

=TEXT(B3, "0 m")

Character m is a placeholder character for a month number, however, we want to use it as the abbreviation for the meter (metric system) in the example above in cell C3. The formula returns a #VALUE! error.

Cell C4 has a backslash character before the m character.

Note! Check your regional settings if the format codes above don't work for you. Win 10 users can open the Control Panel and then press with left mouse button on "Regional settings" to view your settings.

3. Numbers formatting

3.1 How to round numbers - TEXT function

The number of digit symbols you use after the decimal determines which number you want to round.

You can round to thousands and millions and so on using the comma character.

3.2 Number formatting - thousands and millions

The comma symbol scales the number by a thousand, two comma symbols scale the number by a million (1000 * 1000 = 1,000,000).

K = 1,000 and M = 1,000,000

The \ (backslash) symbol escapes the M character so Excel displays the letter M instead of converting the number into minutes.

3.3 Show leading zeros

The 0 (zero) symbol allows you to show leading zeros.

3.4 Format positive, negative and zero values differently

The semicolon character lets you specify formatting code for positive, negative, zero values and text values. The picture below shows you these settings: 0.0;-0.00;"Zero";@

The first position is formatting code for positive values, here I have chosen 0.0. All positive values are rounded to one decimal.

The second position is affecting negative values, in this case, all negative values are rounded to two decimals.

The third position changes 0 to Zero and the fourth position shows the text value.

4. Hide values using the TEXT function

4.1 How to hide positive numbers

Positive values are hidden if you leave out the first position.

4.2 How to hide negative numbers

Negative values are hidden if you leave the second position empty.

4.3 How to hide zeros

If you leave the third position empty zeros are hidden.

4.4 How to hide text values

Omit the fourth position to hide text values.

5. Custom groups

Group 3 is assigned to values larger than 150, Group 2 is assigned to values larger than 100, Group 1 is assigned to all other values.

6. Fractions

The division symbol lets you create fractions.

7. Phone numbers

The 0 (zero) symbol lets you create phone numbers with leading 0's.

8. Indents

Use the underscore character _ and then the character you want to use as an indent. The character is not shown.

The example below uses - (minus) as the indent character in the first position (positive numbers) to align negative and positive numbers. The first row is not aligned.

9. Singular / Plural

10. Excel *.xlsx file

Get the Excel file

TEXT-function.xlsx

'TEXT' function examples

The following 23 articles contain the TEXT function.

7 days (weekly) date ranges using a formula
Shannon asks:I need a formula that if I enter a start date in field B1 such as 6/8/11 it will […]

Add buy and sell points to a stock chart
The image above shows an Excel chart of the S&P 500 with buy and sell signals based on a 50 […]

Count a specific weekday in a date range
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]

Count complete hours between two times
The formula in cell D5 calculates the number of complete hours between the time entries in cell B5 and C5. […]

Count weekday within date range except holidays
Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]

Create a date range [Formula]
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

Create a list of dates with blanks between quarters
Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]

Create a monthly date range
I will demonstrate three different techniques to build monthly date ranges in this article. Two of these techniques are easy because they […]

Create a quartely date range
I will demonstrate three different methods to build quarterly date ranges in this article. The two first methods have a […]

Create number series
Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]

Extract unique distinct year and months from dates
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]

Extract week ranges based on a given date range
The formula in cell B7 and C7 extracts whole weeks within the given date range in cell B3 and C3. […]

Find date range based on a date
This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]

Highlight a date occuring [Conditional formatting]
Excel has a built-in feature that allows you to highlight dates if a given condition is met. Section 1 below […]

How to calculate and plot pivots on an Excel chart
If you study a stock chart you will discover that sometimes significant trend reversals happen when a stock chart […]

How to convert radians to fractions of pi
The formula in cell D3 converts the radian decimal value to fractions of pi. Formula in cell D3: =TEXT(C3/PI(),"?/?")&"*"&CHAR(182) Explaining […]

How to create running totals
This article demonstrates a formula that calculates a running total. A running total is a sum that adds new numbers […]

How to extract numbers from a cell value
The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]

How to highlight dates based on day of week
The following conditional formatting formula highlights dates based on day of the week. =TEXT(B3,"DDDD")=$D$3 The TEXT function converts a value […]

How to remove numbers from a cell value
This article demonstrates an array formula that extracts all characters except numbers from a cell, cell C3 contains the formula […]

Lookup and return multiple values concatenated into one cell
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Monthly calendar template #2
I have created another monthly calendar template for you to get. Select a month and year in cells A1 and […]

Working with classic ciphers in Excel
What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]

Functions in this article

TEXT

Functions in 'Text' category

The TEXT function function is one of many functions in the 'Text' category.

ASC function

Converts full-width (double-byte) characters to half-width (single-byte) characters.

CHAR function

Converts a number to the corresponding ANSI character determined by your computers character set.

CLEAN function

Deletes the first 32 nonprinting characters in 7-bit ASCII code in your argument.

CODE function

Returns the corresponding number for the first character based on your computers character set. (PC- ANSI)

CONCAT function

Concatenates values from multiple cells.

CONCATENATE function

Joins text strings.

EXACT function

Checks if two values are precisely the same, it returns TRUE or FALSE. The EXACT function also considers upper case and lower case letters.

FIND function

Returns the position of a specific string in another string, reading left to right. Note, the FIND function is case-sensitive.

LEFT function

Extracts a specific number of characters always starting from the left.

LEN function

Returns the number of characters in a cell value.

LOWER function

Converts a value to lower case letters.

MID function

Returns a substring from a string based on the starting position and the number of characters you want to extract.

REPLACE function

Substitutes a part of a text string based on the number of characters and length with a text string you provide.

REPT function

Repeats a specific text a chosen number of times.

RIGHT function

Extracts a specific number of characters always starting from the right.

SEARCH function

Returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive)

SUBSTITUTE function

Replaces a specific text string in a value. Case sensitive.

T function

Returns a text value if the argument is a text value.

TEXT function

Converts a value to text in a specific number format.

TEXTAFTER function

Extracts a string after a specific substring in a given value.

TEXTBEFORE function

Extracts a string before a specific substring from a given value.

TEXTJOIN function

Combines text strings from multiple cell ranges.

TEXTSPLIT function

Splits a string into an array based on delimiting values.

TRIM function

Deletes all blanks or space characters except single blanks between words in a cell value.

UNICHAR function

Calculates a character based on a Unicode number.

UNICODE function

Returns a Unicode number based on a character.

UPPER function

Converts a value to upper case letters.

VALUE function

Converts a text string that represents a number to a number.

Excel function categories

Array manipulation

Excel functions that let you resize, combine, and shape arrays.

Compatibility

Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.

Database

Perform basic operations to a database-like structure.

Date and Time

Functions that let you perform calculations to Excel date and time values.

Engineering

Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.

Financial

Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.

Information

Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.

Logical

Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.

Lookup and reference

These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.

Math and trigonometry

You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.

Statistical

Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.

Text

Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.

Web

Get data from the internet, extract data from an XML string and more.

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

2 Responses to “How to use the TEXT function”

Meni Porat says:

February 5, 2022 at 5:21 pm

TEXT can also be used for some mathematical operations.
Suppose you have the number 52 in cell A1 and you want to multiply it by 100.
So, you can use the following formula:
=TEXT(A1,"0\0\0")
and the result will be: 5200.

Even though Excel considers the result as text, you can still perform mathematical operations on it.

Reply
Marco says:

February 15, 2022 at 3:55 am

Trying to figure out how I can turn a number, for example:

0111111111 to 0111.11.11.11 using a function?

Thanks!

M

Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form