Cellwise

Text function

Excel VALUE function

VALUE converts text that looks like a number into an actual number. Use it when imported figures will not add up, or after LEFT, MID, or SUBSTITUTE return digits as text.

fx=VALUE(text)

Arguments

text
The text to convert, such as "42", "15%" or a cell holding a number as text.

VALUE examples

Every result below was calculated live from this sample sheet.

ABC
1Imported qtyImported discountCode
212 40 BX-0045
3812.5%BX-0120
4155%BX-0007
  1. fx=VALUE(A2)*2

    Result: 24

    Turns the text "12" into the number 12, so the math works: 24.

  2. fx=VALUE(B3)

    Result: 0.125

    Percent text converts to a decimal: "12.5%" becomes 0.125.

  3. fx=VALUE(RIGHT(C3,4))

    Result: 120

    RIGHT returns "0120" as text. VALUE turns it into 120.

  4. fx=VALUE("N/A")

    Result: #VALUE!

    Text that is not a number gives #VALUE!.

How to use VALUE

  1. Click an empty cell next to the text number.
  2. Type =VALUE( and select the cell.
  3. Close the bracket and fill down.
  4. Paste the results as values if you want to replace the originals.

Common VALUE mistakes

Hidden spaces

Non-breaking spaces from web data can stop VALUE working. Clean with SUBSTITUTE(A2,CHAR(160),"") first.

Converting real words

VALUE("N/A") is #VALUE!. Wrap it in IFERROR if some cells are not numbers.

VALUE FAQ

How do I convert text to numbers in Excel?

Use =VALUE(A2), or select the cells, click the warning icon, and choose Convert to Number. Multiplying by 1 also works.

Why is my SUM returning 0?

The numbers are probably stored as text, often shown left aligned with a green triangle. Convert them with VALUE first.

Is VALUE the same as NUMBERVALUE?

They are similar, but NUMBERVALUE lets you set the decimal and thousands separators, which helps with European formats like 1.250,00.

Practice VALUE in these lessons

Guides that use VALUE

Related functions

Updated 2026-10-31