Learn how to copy only numbers from a specified numerical range across multiple columns in Google Sheets using a single formula.

Imagine you have several columns in a Google Sheets file filled with numbers, dates, and text. You want to extract only the numbers from these columns into a new range, leaving out dates and text, using a single formula. Here’s how to do it.

=ArrayFormula(
   N(range)*
   NOT(IFERROR(DATEVALUE(range)))*
   NOT(IFERROR(TIMEVALUE(range)))
)

• range: Replace this with your actual cell range. The range can be one- or two-dimensional, but this tutorial focuses on two-dimensional ranges.

Example: Extracting Only Numbers from Multiple Columns

Let’s say you have mixed data in the range B2:E8, which includes numbers representing the number of employees present each day, along with text like “Nil” or “Holiday.” You want to extract only the numeric data into a new range, leaving other cells as 0.

Formula:

=ArrayFormula(
   N(B2:E8)*
   NOT(IFERROR(DATEVALUE(B2:E8)))*
   NOT(IFERROR(TIMEVALUE(B2:E8)))
)

How the Formula Works

This formula consists of three main components:

1. N(range)
   • Converts all values in the range to numeric form.
   • The formula returns numbers, dates, and times as their numeric equivalents.
   • Non-numeric text is converted to 0.
2. NOT(IFERROR(DATEVALUE(range)))
   • DATEVALUE(range) attempts to convert each value to a date.
   • Non-date values result in errors, which IFERROR replaces with 0.
   • NOT(...) converts these 0s to TRUE and date values to FALSE.
3. NOT(IFERROR(TIMEVALUE(range)))
   • Works similarly to the above but checks for time values instead of dates.

Multiplying the outputs of these three parts:

• Only numeric values are retained.
• Dates and times (numeric equivalents) are excluded because they are converted to FALSE by the second and third components.

Additional Tip: Copy Numbers in a Specific Range

To copy only numbers within a specific range (e.g., between 5 and 10, inclusive), modify the formula as follows:

=ArrayFormula(
   N(B2:E8)*
   (B2:E8>=5)*
   (B2:E8<=10)*
   NOT(IFERROR(DATEVALUE(B2:E8)))*
   NOT(IFERROR(TIMEVALUE(B2:E8)))
)

Explanation of Changes

• (range >= 5): Evaluates to TRUE for values greater than or equal to 5.
• (rane <= 10): Evaluates to TRUE for values less than or equal to 10.
• Multiplying these conditions ensures only numbers in the specified range are retained.

Resources

For more related tips, check out:

• Extract All Numbers from Text and Sum Them in Google Sheets
• Extract Numbers Within Square Brackets in Each Row in Google Sheets
• Extract, Replace, Match Nth Occurrence of a String or Number in Google Sheets
• Extract Numbers Prefixed by Currency Signs from a String in Google Sheets
• Extracting Numbers Excluding Dates from a Range in Google Sheets
• How to Extract Decimal Part of a Number in Google Sheets
• How to Extract Negative Numbers from Text Strings in Google Sheets