How to use the TEXT function

What is the TEXT function?

The TEXT function is a helpful tool that allows you to change how a number looks. You can use it to make numbers more readable or to combine them with words or symbols. Think of it like formatting a cell, but instead of using the formatting options in the toolbar, you're using a special formula to get the look you want. This can be especially useful when you want to display numbers in a specific way, like as currency or a date, or when you want to add text to a number.

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.

1. Syntax

TEXT(value, format_text)

Converts a value to text in a specific number format.

2. 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.

3. How to create your own custom formatting

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.

1. Positive values
2. Negative values
3. Zero values
4. Text values

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

Formula in cell D3:

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:

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

3.1 How to convert negative numbers to positive numbers?

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.

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.

Back to top

3.2 How to convert positive numbers to negative numbers?

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

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

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

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

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

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.

Back to top

4. 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.

4.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:

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.

Back to top

4.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:

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

Back to top

4.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:

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

Back to top

4.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:

Back to top

4.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:

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

Back to top

4.6 How to create a percentage

The picture above demonstrates how to format numbers as percentages.

Formula in cell D3:

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

Back to top

4.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:

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

Back to top

4.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

Back to top

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

Formula in cell D3:

=TEXT(B3, 0 " cars")

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

Back to top

4.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.

4.10.1 How to extract the month number without a 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

Back to top

4.10.2 How to extract the month number with a 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

Back to top

4.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.

Back to top

4.10.4 How to extract the full month from a date?

mmmm - Full month name.

Back to top

4.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

Back to top

4.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

Back to top

4.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.

Back to top

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

dddd - Full day name.

Back to top

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

yy - Year as a two-digit number.

Back to top

4.10.6 How to extract the full year from a date?

yyyy - Year as a four-digit number.

Back to top

4.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.

4.12 Why isn't the TEXT function working?

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.

Troubleshooting the error value

When you encounter an error value in a cell a warning symbol appears, displayed in the image above. Press with mouse on it to see a pop-up menu that lets you get more information about the error.

1. The first line describes the error if you press with left mouse button on it.
2. The second line opens a pane that explains the error in greater detail.
3. The third line takes you to the "Evaluate Formula" tool, a dialog box appears allowing you to examine the formula in greater detail.
4. This line lets you ignore the error value meaning the warning icon disappears, however, the error is still in the cell.
5. The fifth line lets you edit the formula in the Formula bar.
6. The sixth line opens the Excel settings so you can adjust the Error Checking Options.

Here are a few of the most common Excel errors you may encounter.

#NULL error - This error occurs most often if you by mistake use a space character in a formula where it shouldn't be. Excel interprets a space character as an intersection operator. If the ranges don't intersect an #NULL error is returned. The #NULL! error occurs when a formula attempts to calculate the intersection of two ranges that do not actually intersect. This can happen when the wrong range operator is used in the formula, or when the intersection operator (represented by a space character) is used between two ranges that do not overlap. To fix this error double check that the ranges referenced in the formula that use the intersection operator actually have cells in common.

#SPILL error - The #SPILL! error occurs only in version Excel 365 and is caused by a dynamic array being to large, meaning there are cells below and/or to the right that are not empty. This prevents the dynamic array formula expanding into new empty cells.

#DIV/0 error - This error happens if you try to divide a number by 0 (zero) or a value that equates to zero which is not possible mathematically.

#VALUE error - The #VALUE error occurs when a formula has a value that is of the wrong data type. Such as text where a number is expected or when dates are evaluated as text.

#REF error - The #REF error happens when a cell reference is invalid. This can happen if a cell is deleted that is referenced by a formula.

#NAME error - The #NAME error happens if you misspelled a function or a named range.

#NUM error - The #NUM error shows up when you try to use invalid numeric values in formulas, like square root of a negative number.

#N/A error - The #N/A error happens when a value is not available for a formula or found in a given cell range, for example in the VLOOKUP or MATCH functions.

#GETTING_DATA error - The #GETTING_DATA error shows while external sources are loading, this can indicate a delay in fetching the data or that the external source is unavailable right now.

The formula returns an unexpected value

To understand why a formula returns an unexpected value we need to examine the calculations steps in detail. Luckily, Excel has a tool that is really handy in these situations. Here is how to troubleshoot a formula:

1. Select the cell containing the formula you want to examine in detail.
2. Go to tab “Formulas” on the ribbon.
3. Press with left mouse button on "Evaluate Formula" button. A dialog box appears.
   The formula appears in a white field inside the dialog box. Underlined expressions are calculations being processed in the next step. The italicized expression is the most recent result. The buttons at the bottom of the dialog box allows you to evaluate the formula in smaller calculations which you control.
4. Press with left mouse button on the "Evaluate" button located at the bottom of the dialog box to process the underlined expression.
5. Repeat pressing the "Evaluate" button until you have seen all calculations step by step. This allows you to examine the formula in greater detail and hopefully find the culprit.
6. Press "Close" button to dismiss the dialog box.

There is also another way to debug formulas using the function key F9. F9 is especially useful if you have a feeling that a specific part of the formula is the issue, this makes it faster than the "Evaluate Formula" tool since you don't need to go through all calculations to find the issue..

1. Enter Edit mode: Double-press with left mouse button on the cell or press F2 to enter Edit mode for the formula.
2. Select part of the formula: Highlight the specific part of the formula you want to evaluate. You can select and evaluate any part of the formula that could work as a standalone formula.
3. Press F9: This will calculate and display the result of just that selected portion.
4. Evaluate step-by-step: You can select and evaluate different parts of the formula to see intermediate results.
5. Check for errors: This allows you to pinpoint which part of a complex formula may be causing an error.

The image above shows cell reference C3 converted to hard-coded value using the F9 key. The TEXT function requires valid format code which is not the case in this example. We have found what is wrong with the formula.

Tips!

• View actual values: Selecting a cell reference and pressing F9 will show the actual values in those cells.
• Exit safely: Press Esc to exit Edit mode without changing the formula. Don't press Enter, as that would replace the formula part with the calculated value.
• Full recalculation: Pressing F9 outside of Edit mode will recalculate all formulas in the workbook.

Remember to be careful not to accidentally overwrite parts of your formula when using F9. Always exit with Esc rather than Enter to preserve the original formula. However, if you make a mistake overwriting the formula it is not the end of the world. You can “undo” the action by pressing keyboard shortcut keys CTRL + z or pressing the “Undo” button

Other errors

Floating-point arithmetic may give inaccurate results in Excel - Article

Floating-point errors are usually very small, often beyond the 15th decimal place, and in most cases don't affect calculations significantly.

Back to top

5. Numbers formatting

5.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.

Back to top

5.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.

Back to top

5.3 Show leading zeros

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

Back to top

5.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.

Back to top

6. Hide values using the TEXT function

6.1 How to hide positive numbers

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

Back to top

6.2 How to hide negative numbers

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

Back to top

6.3 How to hide zeros

If you leave the third position empty zeros are hidden.

Back to top

6.4 How to hide text values

Omit the fourth position to hide text values.

Back to top

7. 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.

Back to top

8. Fractions

The division symbol lets you create fractions.

Back to top

9. Phone numbers

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

Back to top

10. 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.

Back to top

11. Singular / Plural

Back to top

Back to top

Useful links

TEXT function - Microsoft
TEXT function

'TEXT' function examples

Functions in 'Text' category

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

Excel function categories

Excel categories

Comments (2)