Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 93 ยท Text, Date and Time

TEXT, VALUE, NUMBERVALUE and Number-as-Text Problems

Format numbers and dates with TEXT, convert text back with VALUE and NUMBERVALUE, and fix numbers stored as text, lost leading zeros and locale problems.

Excel keeps two worlds apart: numbers (which you can add, compare and chart) and text (which you can join and search). Most surprising totals come from a value that looks like a number but is really text, or from a number that was glued into a sentence and lost its formatting on the way. Three functions sit at the border: TEXT turns a number into formatted text, VALUE turns number-like text back into a number, and NUMBERVALUE does the same while letting you state the decimal and group separators.

In this lesson you will learn the common TEXT format codes, how to recognize and repair numbers stored as text, how to keep leading zeros, and why regional settings can silently change results. Excel cannot run on this site, so a JavaScript stand-in implements the format codes and conversions you can try, with a Python version at the end. It builds on Entering and Editing Data and Formatting Cells.

Crossing the border

TEXT Number to formatted text.
VALUE Number-like text to a number.
NUMBERVALUE Same, with your own separators.
1 / 2

TEXT: format a value as text

TEXT(value, format_text) returns the value as text, shaped by a format code, the same kind of code you type under Custom in Format Cells. Use it when a number must go into a sentence or label, as in ="Total: " & TEXT(B2, "#,##0.00"). Without TEXT, the join would use the raw General value, and a date would show up as its serial number.

The most useful codes:

Code Meaning TEXT(1234.567, ...)
0 Digit, zero if missing "0" gives 1235
0.00 Always 2 decimals 1234.57
#,##0 Thousands separator 1,235
#,##0.0 Thousands, 1 decimal 1,234.6
00000 Pad to 5 digits 01235
0% Multiply by 100, add % 123457%
$0.00 Literal prefix $1234.57
0 "units" Literal text in quotes 1235 units

# shows a digit only if there is one, 0 shows a zero instead. For dates and times:

Code Meaning
yyyy, yy Year, four or two digits
mmmm, mmm, mm, m Month: full name, short name, two digits, plain
dddd, ddd, dd, d Day: weekday name, short weekday, two digits, plain
hh:mm, h:mm AM/PM 24-hour or 12-hour time
ss Seconds

The letter m means month by default and minutes right after h or right before s. Codes are case-insensitive for dates, and anything inside double quotes is copied as is.

The format codes inside TEXT depend on the language of your Excel. A German Excel expects TT.MM.JJJJ where an English one expects dd.mm.yyyy, and the decimal and thousands characters follow the system settings. A workbook with TEXT(A1, "yyyy") can show an error or wrong text on a computer set to another language.

Run it: a TEXT stand-in

This function implements a simplified TEXT for the codes in the tables. Dates use the serial numbers explained in Dates and Times in Excel: serial 1 is January 1, 1900 and a time is the fraction of a day. It does not cover every code Excel supports (for example sections separated by ; or [h]).

JS Format codes with a TEXT stand-in

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The last two lines show the reason TEXT exists: joining a date straight into a sentence gives the serial number.

VALUE and NUMBERVALUE: text back to numbers

VALUE(text) converts text that looks like a number, a percentage, a currency amount, a date or a time into a real number, using your computer's regional settings. VALUE("1,234.50") is 1234.5 on a US-style system. If the text does not look like a number, you get #VALUE!.

NUMBERVALUE(text, [decimal_separator], [group_separator]) is stricter and portable, because you say what the separators are:

TEXT
=NUMBERVALUE("1.234,56", ",", ".")    1234.56   (German-style text)
=NUMBERVALUE("1,234.56", ".", ",")    1234.56   (US-style text)
=NUMBERVALUE("12 %")                  0.12      (spaces ignored, % divides by 100)
=NUMBERVALUE("")                      0         (empty text counts as zero)

For data that comes from another country, NUMBERVALUE is the safe choice.

Numbers stored as text

How does a number become text? Typical causes: the cell was formatted as Text before typing, the value began with an apostrophe, the data came from a CSV, a web page or another system, or it has extra spaces, a non-breaking space (character 160) or a currency symbol attached.

You can spot them by:

  • left alignment in a General-formatted cell,
  • the green triangle in the corner, with the menu option Convert to Number,
  • =ISNUMBER(A1) returning FALSE,
  • totals that are too small, because SUM ignores text.

Ways to fix them:

  1. Use the Convert to Number option from the warning icon for a selection.
  2. Use Data โ†’ Text to Columns โ†’ Finish on the column; it re-reads every cell.
  3. Type 1 in an empty cell, copy it, select the data and choose Paste Special โ†’ Multiply.
  4. Write a formula: =VALUE(A1), =--A1 or =A1*1. For stray characters, clean first: =VALUE(SUBSTITUTE(TRIM(A1), CHAR(160), "")). See Text Functions.

Changing the cell format to General does not fix existing text. Excel keeps the stored text until you re-enter the cell.

Run it: spot and repair

The stand-in below shows SUM ignoring text, then converts a list of messy inputs with a VALUE-style cleaner and a NUMBERVALUE. These are simplified: the real VALUE understands more formats.

JS Numbers stored as text: spot and repair

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The last line is the locale trap in one example. The text 1.234 is one point two three four in a US-style system and one thousand two hundred thirty-four in a German-style system. A CSV file opened on the "wrong" computer is converted without any warning. See Importing Data from CSV, Text and the Web for choosing delimiters and locales on import.

Leading zeros and long numbers

IDs such as ZIP codes (02134), phone numbers and product codes are labels, not quantities. Excel drops their leading zeros if it reads them as numbers. You have two ways to show them:

  • Store as text: format the cells as Text before typing, or begin the entry with an apostrophe. Formula: =TEXT(A2, "00000") returns text padded to five digits (use it when you receive numbers that lost their zeros).
  • Keep a number, change the look: apply the custom number format 00000. The cell value stays 2134; only the display is 02134. Lookups and joins still see 2134, which is a common source of mismatches.

Numbers also have a limit of 15 significant digits. Anything longer, like a 16-digit card number, loses its last digits when entered as a number, so such identifiers must be text.

The same idea in Python

Python separates the two worlds too, with f-strings for TEXT and int, float or pandas for VALUE.

Python TEXT and VALUE in Python

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that pd.to_numeric(..., errors="coerce") marks unreadable values as missing instead of failing, which is a useful pattern when cleaning, as in Cleaning Messy Data Step by Step.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does ="Due: " & A1 show when A1 holds a date?

    The ampersand converts the date with General format, which gives its serial number. Use TEXT(A1, "mmm d, yyyy") to format it.
  2. 2. What does =TEXT(42, "00000") return?

    Each 0 in the code forces a digit, so 42 is padded with leading zeros. The result is text, not a number.
  3. 3. SUM returns less than you expect, and some values are left-aligned. What is the most likely cause?

    Left alignment and a green triangle point to numbers stored as text. Convert them to real numbers, for example with VALUE or Text to Columns.
  4. 4. Why is NUMBERVALUE safer than VALUE for text from another country?

    VALUE uses your regional settings, so "1.234" is read differently on different computers. NUMBERVALUE takes explicit separators.
  5. 5. You apply the custom format 00000 to a cell containing 2134. What does a lookup find?

    A number format changes how a value is shown, not what is stored. The cell still holds the number 2134.

Summary

  • TEXT(value, format) returns formatted text; use it before joining numbers or dates into sentences. Its format codes depend on the Excel language.
  • VALUE converts number-like text using regional settings; NUMBERVALUE lets you state the separators and is better for foreign data.
  • Left alignment, green triangles, ISNUMBER and low totals reveal numbers stored as text; fix them with Convert to Number, Text to Columns, Paste Special Multiply or a formula.
  • Keep leading zeros by storing IDs as text, or by using a number format that only changes the display.
  • Numbers keep 15 significant digits, and locale differences can silently change how 1.234 is read.

Next: Dates and Times in Excel.