How to Use TO_PURE_NUMBER Function in Google Sheets

This guide will explain how to use the TO_PURE_NUMBER function in Google Sheets.

When handling numerical data in Google Sheets, your data may be formatted as a date, time, currency, and so on. While the number displayed on the sheet is formatted, you may need to use just the underlying numerical value. For example, the date “12/25/1995” has the numerical value of 35058.

The plain numerical value behind the formatted value is known as a pure number. Converting to pure numbers allow you to work with your data without having to deal with formatting inconsistencies and to ensure your dataset is standardized and consistent.

In this guide, we will provide a step-by-step tutorial on how to convert any numerical value in Google Sheets into a pure number using TO_PURE_NUMBER. We will cover converting various types of data into pure numbers such as percentages, dates, times, and currencies.

The Anatomy of the TO_PURE_NUMBER Function

The syntax of the TO_PURE_NUMBER function is as follows:

=TO_PURE_NUMBER(value)

Let’s look at each argument to understand how to use the TO_PURE_NUMBER function.

• = the equal sign is how we start any function in Google Sheets.
• TO_PURE_NUMBER() refers to our TO_PURE_NUMBER function. This function allows us to convert any formatted numeric value into a numerical number without any formatting.
• value argument can be a numerical value or cell reference that you wish to convert into a pure number.
• If value does not contain any numeric value, the TO_PURE_NUMBER function returns value without any changes.
• The TO_PURE_NUMBER function is similar to the setting Format > Number > Normal from the menu bar. One difference is that the Normal format output still includes commas denoting thousands, millions, and so on.

A Real Example of Using TO_PURE_NUMBER Function in Google Sheets

Let’s explore a simple example where we can use the TO_PURE_NUMBER function in Google Sheets.

In the table above, we have a value in cell A2 formatted in the Accounting number format. We would like to convert this value into its pure number equivalent.

To do this, we can use the following formula:

=TO_PURE_NUMBER(A2)

The TO_PURE_NUMBER function takes a single cell as input and returns the underlying numerical value without formatting.

In our example above, we can now see that the value returned in cell B2 does not contain the currency symbols or commas found in the original value.

Let’s now try to convert other types of numerical data.

In the example above, we used the TO_PURE_NUMBER function multiple times to convert different formatted numerical values into their pure number equivalents.

For example, we convert the time value “9:45” to its decimal equivalent. The date value “6/20/2024” is converted to its integer date value. We calculate this date value as the number of days since December 31st, 1899

We can combine the TO_PURE_NUMBER function with the ARRAYFORMULA function to allow us to convert an entire array of formatted numbers into pure numbers.

In the example above, we used the formula ARRAYFORMULA(TO_PURE_NUMBER(A2:A6)) to convert our range of currency values into pure numerical values.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to start using the TO_PURE_NUMBER function in Google Sheets.

How to Use TO_PURE_NUMBER Function in Google Sheets

1. First, select an empty cell where you wish to output the result of the TO_PURE_NUMBER function. Then type “=TO_PURE_NUMBER(“ to start the function.
   
   In this example, we’ll enter our function in cell B2.
2. Enter the number or cell reference you wish to convert into a pure number. Hit Enter to evaluate the function.
   
   In the example above, we converted the formatted currency value in cell A2 into a pure number.
3. We can also use the ARRAYFORMULA function with TO_PURE_NUMBER to convert an entire range of formatted numerical values.
   
   In our example above, we used the formula =ARRAYFORMULA(TO_PURE_NUMBER(A2:A6)) to convert the currency values in the range A2:A6 into an array of pure numbers.

FAQs

1. How do pure numbers help with data integrity?
   Pure numbers minimize the risk of errors associated with misinterpreting data formats. For example, in financial analysis or scientific research, converting your data into pure numbers allows the user to confidently handle their data without having to worry about formatting issues.
   
2. What is the difference between TO_PURE_NUMBER and the N function?
   When handling formatted numerical data, both TO_PURE_NUMBER and N return similar output. The functions differ in their handling on non-numeric data.
   The N function returns 0 for most non-numeric values. Meanwhile, the TO_PURE_NUMBER does not modify non-numeric arguments.

To learn more about converting numerical values in Google Sheets, you can read our post on how to convert time to decimal in Google Sheets.

That’s all for this guide! Don’t forget to check out our library of spreadsheet resources, tips, and tricks for both Google Sheets and Microsoft Excel!