The TO_PURE_NUMBER function in Google Sheets serves the essential purpose of converting various formatted numeric values, such as dates, percentages, and currencies, into a clean, unformatted number. This function is especially useful for data analysis, as it allows users to easily manipulate and perform calculations on numeric data without the interference of formatting.

Syntax

TO_PURE_NUMBER(value)

• value: The numeric value you want to convert. This can be a date, time, percentage, currency, or any other formatted number.

Example #1

TO_PURE_NUMBER("$150")

This function call converts the currency value of $150 into the pure number 150. Result: 150

Example #2

TO_PURE_NUMBER("50%")

Here, this function processes the percentage value of 50% and converts it into the pure number 0.5. Result: 0.5

Example #3

TO_PURE_NUMBER(DATE(2023, 12, 31))

This function takes a date formatted as December 31, 2023, and returns its serial number in Google Sheets, which represents the days since a specific base date. Result: 45237

Error handling

• VALUE! – This error occurs if the provided value cannot be interpreted as a number.
• N/A – This error is displayed when the function references an invalid or empty cell.
• REF! – This indicates that a reference provided in the function is not valid, possibly due to deleted cells.

Conclusion

The TO_PURE_NUMBER function is a powerful tool in Google Sheets that enhances data handling by removing formatting from numeric values. It is invaluable for users who require unformatted numbers for various calculations and analyses, ensuring cleaner and more efficient data management.