What's the difference between TO_PURE_NUMBER and VALUE?

VALUE() converts simple text to a number but fails on formatted values. TO_PURE_NUMBER is designed specifically to strip formatting (currency symbols, percentage signs, commas) and extract the pure numeric value. For a cell displaying $100, VALUE might error but TO_PURE_NUMBER returns 100.

How do I use TO_PURE_NUMBER on a CSAT column stored as percentages?

If CSAT is '92%' or '78%', apply =TO_PURE_NUMBER(F2) to extract 92 or 78. Then use it in calculations like =AVERAGE(ARRAYFORMULA(TO_PURE_NUMBER(F:F))) to get the mean CSAT score. This removes the % formatting automatically.

Why does TO_PURE_NUMBER fail on my agent or ticket ID columns?

TO_PURE_NUMBER fails (#VALUE!) when a cell contains pure text with no number in it, like 'Sarah' or 'TK-2401'. It only works on columns that include or represent numbers, such as priority, CSAT, or calculated time fields.

Should I use TO_PURE_NUMBER or REGEX to extract numbers from ticket text?

If the entire cell is a number with formatting ('92%', '$100'), use TO_PURE_NUMBER directly. If the number is embedded in longer text ('Priority: 3 - High'), use REGEXEXTRACT() to isolate it first, then pass the result to TO_PURE_NUMBER.

TO_PURE_NUMBER function

Converts a value to its pure numeric form, removing formatting like currency, percentage, or text labels to expose the underlying number.

=TO_PURE_NUMBER(value)

Generate a TO_PURE_NUMBER formula

Describe what you need. The generator will reach for TO_PURE_NUMBER where TO_PURE_NUMBER is the right tool, and tell you when it is not.

Arguments

How TO_PURE_NUMBER reads its arguments

Argument	Required	Description
value	Required	Any value—number, text, date, or formatted cell. If the value contains no numeric component, returns #VALUE! error.

Returns

A number representing the numeric value of the input, with all formatting stripped away.

Availability

Excel: Not available · Google Sheets: Supported

Worked examples

1. Extract priority level from text-formatted field

Ticket ID	Priority	Opened
TK-2401	3	2026-01-15
TK-2402	1	2026-01-16
TK-2403	2	2026-01-17

=TO_PURE_NUMBER(A2)

Result: 3

When priority levels are stored as text (e.g., '3'), TO_PURE_NUMBER converts the text string to the numeric value 3. This is essential before using priority in calculations like SUM or AVERAGE across tickets.

2. Convert CSAT percentage to numeric value for reporting

Ticket ID	Agent	Opened	Closed	Priority	CSAT
TK-2401	Sarah	2026-01-15	2026-01-18	2	92%
TK-2402	James	2026-01-16	2026-01-20	3	78%
TK-2403	Maria	2026-01-17	2026-01-21	1	85%

=TO_PURE_NUMBER(F2)

Result: 92

CSAT stored as percentage text '92%' appears formatted but is stored as text. TO_PURE_NUMBER extracts the numeric value 92, allowing you to calculate average CSAT (e.g., AVERAGE(TO_PURE_NUMBER(F2:F4)) = 85).

3. Extract numeric days from formatted resolution time

Ticket ID	Opened	Resolution Time	Agent
TK-2401	2026-01-15	3 days	Sarah
TK-2402	2026-01-16	4 days	James
TK-2403	2026-01-17	4 days	Maria

=TO_PURE_NUMBER(C2)

Result: 3

When resolution time is calculated and formatted as '3 days' using TEXT(), it becomes text. TO_PURE_NUMBER strips the ' days' suffix and returns 3, letting you then calculate median resolution time or find outliers.

Common errors

Which TO_PURE_NUMBER error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The value contains only non-numeric text with no number embedded, such as =TO_PURE_NUMBER(B2) where B2 contains 'Agent Name' or 'Sarah'. TO_PURE_NUMBER cannot extract a number from pure text.	Ensure the value contains or represents a number. Use REGEX(value, '[0-9]+') first to extract digits from text, then apply TO_PURE_NUMBER to the result.
#N/A	The input is a reference to a cell that does not exist or is outside the sheet's bounds, such as =TO_PURE_NUMBER(Z999) when that row has no data, or referencing a deleted range.	Verify the cell reference points to valid data. Wrap with IFERROR() to handle missing cells: =IFERROR(TO_PURE_NUMBER(A2), 0).
#NUM!	The value cannot be coerced to a number because it contains invalid numeric characters or an unrecognized date format, such as =TO_PURE_NUMBER('13/45/2026') which is not a valid date.	Validate the input is in a recognized format (e.g., dates as MM/DD/YYYY or YYYY-MM-DD, numbers without excess symbols). Use DATEVALUE() to normalize dates before applying TO_PURE_NUMBER.

Tips and when to use something else

Use TO_PURE_NUMBER when formatted or text-wrapped numbers need to be used in calculations. For support ticket CSAT scores stored as '92%', it extracts 92 immediately.
TO_PURE_NUMBER is not the same as VALUE(). VALUE() converts pure text to numbers; TO_PURE_NUMBER removes formatting. For '$100', TO_PURE_NUMBER returns 100, while VALUE('$100') may fail.
Combine TO_PURE_NUMBER with REGEX() when text contains multiple numbers and you need only one: =TO_PURE_NUMBER(REGEXEXTRACT(A2, '[0-9]+')) extracts the first number from 'Priority: 3 - High'.
If your ticket priority or duration columns are formatted as text, wrap them with TO_PURE_NUMBER in summary formulas: =AVERAGE(ARRAYFORMULA(TO_PURE_NUMBER(A2:A100))) to average priority across all tickets.

Related functions

TO_TEXTConverts a value to text format, returning its content as plain text regardless of the original data type or formatting.VALUEVALUE converts a text string that looks like a number, currency, or date into the underlying numeric value Excel or Sheets can use in calculations.TEXTTEXT converts a numeric, date, or time value into a formatted text string based on a custom format pattern you supply.ARRAYFORMULAARRAYFORMULA applies a single formula to an entire array or range, automatically filling results into multiple cells.

Frequently asked questions

What's the difference between TO_PURE_NUMBER and VALUE?: VALUE() converts simple text to a number but fails on formatted values. TO_PURE_NUMBER is designed specifically to strip formatting (currency symbols, percentage signs, commas) and extract the pure numeric value. For a cell displaying $100, VALUE might error but TO_PURE_NUMBER returns 100.
How do I use TO_PURE_NUMBER on a CSAT column stored as percentages?: If CSAT is '92%' or '78%', apply =TO_PURE_NUMBER(F2) to extract 92 or 78. Then use it in calculations like =AVERAGE(ARRAYFORMULA(TO_PURE_NUMBER(F:F))) to get the mean CSAT score. This removes the % formatting automatically.
Why does TO_PURE_NUMBER fail on my agent or ticket ID columns?: TO_PURE_NUMBER fails (#VALUE!) when a cell contains pure text with no number in it, like 'Sarah' or 'TK-2401'. It only works on columns that include or represent numbers, such as priority, CSAT, or calculated time fields.
Should I use TO_PURE_NUMBER or REGEX to extract numbers from ticket text?: If the entire cell is a number with formatting ('92%', '$100'), use TO_PURE_NUMBER directly. If the number is embedded in longer text ('Priority: 3 - High'), use REGEXEXTRACT() to isolate it first, then pass the result to TO_PURE_NUMBER.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17