Knowledge base / Excel

VALUE in Excel: numeric text and import separators

I convert numeric text and compare two source formats for the same number, explaining when an identifier should remain text.

Kajo RudzińskiPublished Updated 6 min read

A machine converts hollow frames into solid cubes and separates mismatched pieces

A cell displays 1500, but after an import its value is still text. Changing its colour, alignment or display format does not settle the issue. When I want to explicitly convert a text representation of a number into a number, I use VALUE.

First, I decide whether the column should actually be numeric. An amount and a customer identifier may look similar. Converting the code “0012” to 12 removes leading zeros that might belong to the identifier.

What VALUE accepts

The syntax is short:

=VALUE(text)

The argument can be quoted text or a cell reference. Microsoft describes VALUE as converting text representing a number. It can also recognize date and time formats supported by Excel. That is another reason to check the meaning of a column before converting it in bulk.

If the text does not match a recognized format, the function returns #VALUE!. It does not independently extract an arbitrary number from a product description or infer the meaning of units.

Microsoft documents the function for Microsoft 365 and versions including Excel 2024, 2021, 2019 and 2016. English examples here use commas in formulas with multiple arguments. Argument separators depend on regional settings. In Polish Excel, the corresponding names are WARTOŚĆ and WARTOŚĆ.LICZBOWA for VALUE and NUMBERVALUE.

Start in a helper column

In A2, I enter the text 1500. A leading apostrophe can be used to enter it as text; that entry prefix is not part of the cell’s value. In B2, I write:

=VALUE(A2)

The result is the number 1500. Column A retains the source, while B holds the conversion. I can compare them and return to the original representation when something goes wrong. Microsoft also demonstrates a helper-column approach in its guide to converting numbers stored as text.

Input text Result or decision
1500 number 1500
0012 number 12, if conversion is intended
abc value error
12 units establish and separate the unit first

These are synthetic data. The last row is not a valid numeric text representation to pass directly into VALUE. I avoid mechanically removing the last six characters from an entire import: units and the length of the text may vary.

Decimal separators affect interpretation

The text 12.5 can represent a decimal under settings that use a decimal point. The text 12,5 may need a different interpretation. An English or Polish function name does not by itself define the source file’s conventions.

For an import, I establish the export format first. One cell is insufficient evidence. The text 1,234 could represent a decimal with three fractional digits or an integer with a thousands separator. If the source does not settle that distinction, I need to clarify the data contract.

Removing every comma and point is risky. It can turn 12.5 into 125, leaving a valid numeric type with the wrong value. Checking the type is only part of validation; the separators’ meaning must be preserved.

NUMBERVALUE for a known export format

When I know the source separators, I prefer to state them explicitly using NUMBERVALUE:

=NUMBERVALUE("1,234.56",".",",")

The source uses a decimal point and a comma to group thousands. The result is the number 1234.56, written here with a decimal point. Quotation marks surround the separator characters; commas outside the quotes separate formula arguments.

For the opposite source convention, I use:

=NUMBERVALUE("1.234,56",",",".")

The numeric result is the same. The NUMBERVALUE documentation explains locale-independent conversion when the appropriate separators are supplied. If I omit them, the current environment’s separators are used.

I do not apply one formula blindly to a mixture of indistinguishable formats. If some sources use a decimal comma and others a grouping comma, I need row provenance and the appropriate rule for each group.

Two differently arranged inputs pass through matching gates into identical cubes

Empty input, spaces and errors

Empty input needs an explicit decision. Microsoft specifies that NUMBERVALUE("") returns zero. A missing amount may have a different business meaning from an actual amount of zero. I distinguish missing data before conversion.

Spaces from imports are not always the same character. An ordinary space and a nonbreaking space can look similar. The TRIM documentation warns that TRIM alone does not remove the nonbreaking space. I therefore avoid promising that one cleaning step fixes every export copied from a browser.

For errors, I retain the source and flag rows that need investigation. Automatically replacing errors with zero could reduce a total and hide the problem. Once the cause is known, I document a cleaning rule instead of making unexplained edits to individual cells.

Dates and identifiers need care

VALUE can convert recognized date text into a serial number. The displayed result depends on cell formatting, while text interpretation and the date system affect its meaning. An arbitrary serial number does not establish that a date import succeeded.

For separate year, month and day fields, I prefer explicitly constructing dates with DATE. When the source provides one text column, I first establish its format. The text 01/02 does not independently tell me whether it means January or February.

I preserve identifiers as text when their written form matters. An account number, postal code or customer code does not become something to sum just because it contains digits. Losing leading zeros can make later record matching more difficult.

Finish the conversion deliberately

I inspect rows with different characteristics: a positive integer, a decimal, zero, missing input and invalid text. I also check the number of rejected rows and whether the values fit the expected scale. A result of 125 instead of 12.5 is easy to overlook in a dense table.

Only then do I decide whether to retain the helper column or paste the results as values. A recurring import needs a repeatable rule together with information about the source format. I cover the broader process in the data cleaning guide.

For an exercise, prepare the text values 1500, 0012 and abc. Decide which should become numbers in your scenario. Then add two representations of 1234.56 with different grouping conventions and compare explicit NUMBERVALUE rules. The most useful part is explaining the decisions before the original text disappears.

Key takeaways

Can VALUE extract a number from any description?

No. The text must match a format recognized by Excel. Descriptions with units need a separate preparation rule.

How do I set import separators explicitly?

Use NUMBERVALUE with decimal and grouping separators matching the source format.

Should codes with leading zeros become numbers?

Keep them as text when the zeros belong to the identifier. Numeric conversion can remove meaningful formatting.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.