TO_PURE_NUMBER in Google Sheets: Reveal Underlying Values

• Introduced: first introduction date is not established by the reviewed official history; no desktop release number is inferred.
• Availability: documented in the rolling Google Sheets service; these examples use the web worksheet workflow.
• Checked: October 14, 2026; official TO_PURE_NUMBER reference. Documentation and independent models, with no native Sheets execution or wider client certification.

=TO_PURE_NUMBER(value)

Google states that formatted numeric inputs return their underlying values, while nonnumeric inputs are unchanged. One argument bullet accidentally names TO_PERCENT; the separate comparison with N explicitly repeats TO_PURE_NUMBER’s unchanged-nonnumeric rule and resolves that naming slip.

N is different: its ordinary nonnumeric conversions include text to zero and TRUE to one. Do not substitute it when preserving a label or Boolean matters. The guide does not extend that brief comparison into an unverified universal error-input rule.

Set up these cells:

In B2 use =TO_PURE_NUMBER(A2) and fill through B4. In fresh, unformatted output cells, the numeric results are 0.375, 2500.125 and 12.3.

Removing a percent sign does not imply multiplication by 100. Similarly, removing a currency symbol does not perform an exchange-rate conversion. The function reveals the stored value rather than interpreting the display as a new quantity.

If B already has a manually imposed number format, inspect or clear that display separately before concluding that the function changed the numerical result.

In D2 enter:

=DATE(2024,1,1)+TIME(6,0,0)

Then use =TO_PURE_NUMBER(D2) in E2. The independently calculated expected number is 45292.25. Google’s N reference documents the Sheets date base as days since December 30, 1899; January 1, 2024 is 45292 days after that base, and six hours is 6/24=0.25 day.

This is a serial representation, not a Unix timestamp or a time-zone conversion. Multiplying the fractional part by 24 gives six hours; multiplying the whole serial by 24 also includes all days since the date base.

The example uses a modern date and an explicit DATE constructor, avoiding ambiguous typed date text.

Suppose G2 contains numeric 0.18 displayed as 18%, while G3 contains numeric 18 displayed as 1800%. =TO_PURE_NUMBER(G2) returns 0.18 and the same expression for G3 returns 18.

The underlying values differ by a factor of 100. The function does not decide which is correct; the data specification must say whether the import contains ratios or whole-number percents.

If the specification says 18 means eighteen percent, divide that imported value by 100 before applying percentage formatting. Do not apply the same division to values already stored as 0.18.

Enter text 008, label pending and Boolean =TRUE() in H2:H4. Use =TO_PURE_NUMBER(H2) and fill down. The documented expectations are unchanged text 008, unchanged text pending and unchanged Boolean TRUE.

For comparison, =N(H2) gives 0 for ordinary text 008, while =N(H4) gives 1 for TRUE under Google’s separate N rules. Those are type conversions, not equivalent ways to remove formatting while preserving meaning.

Use ISNUMBER when a downstream calculation requires numeric data. A successful pass-through of pending is still nonnumeric. Use VALUE only when parsing text into a recognized numeric or date/time value is actually intended.

Removing formatting does not restore digits already lost through an earlier rounding operation. If A3 had first been rounded numerically to 2500.13, TO_PURE_NUMBER would expose 2500.13, not reconstruct 2500.125.

Keep unit labels outside the numeric value so a pure number remains interpretable. A bare 0.25 could be a quarter day, a 25% proportion or a measurement in another unit.

TO_PERCENT, TO_DOLLARS and TO_TEXT have distinct presentation and type goals. Native blank/error boundaries and every manual formatting interaction remain outside the controlled examples.

• Google TO_PURE_NUMBER reference.
• Editorial testing standards.

Examples use fictional data, English function names and comma separators. Expected outputs are independent mathematical calculations or explicitly documented rules, not native Sheets captures. Check the spreadsheet locale when reproducing punctuation-sensitive formulas.