How to Copy Only Numbers from Multiple Columns in Google Sheets

Learn how to copy only numbers from multiple Columns in Google Sheets.

I have several columns in a Google Sheets file filled with numbers and text. How can I copy only numbers from these columns to a new range?

I want a single formula to do this. So here is that trick.

You can learn in this Sheets, Google Doc, tutorial how to copy only numbers from multiple columns in Google Sheets.

You can also apply logical If then condition in such copy. Let me explain it first. Further, I’ve given additional formulas to similarly copy only text and use of comparison operators “<” and “>” in such copy and paste.

Example to Copy Only Numbers from Multiple Columns

In this example, I’ve copied the numbers in the range B3: E8 to G3: J8. Let’s see how to copy only numbers from multiple columns in Google Sheets as above.

How to Copy Only Numbers from Multiple Columns in Google Sheets

We can use here ISNUMBER function to test whether a cell content is a text like;

=ISNUMBER(B3)

If the value in Cell B3 is number then the formula would return TRUE. If the returned value is TRUE, then with Google Sheets IF function, we can return that value in cell B3. The formula will be as follows.

=if(ISNUMBER(B3),B3,)

Needless to say, we can use this trick to copy only numbers from multiple columns in Google Sheets. We can use the ArrayFormula as below.

=ArrayFormula(if(isnumber(B3:E8),B3:E8,))

This way you can limit the copy of only numbers from multiple columns.

Some Additional Tips:

With minor changes to the above formula, you can limit the copying of content from numbers to text. I mean you can reverse the above formula.

In Sheet, Google Doc, How to Copy Only Text from Multiple Columns

Here we only need to replace the ISNUMBER function with ISTEXT. The formula to copy only text from several columns will be as follows.

=ArrayFormula(if(ISTEXT(B3:E8),B3:E8,))

I know some of you may want to know how to use comparison operators in multiple column copying.

As an example, copy all the numbers of which the values are “<10” or something similar. See that also.

How to Copy Only Numbers Within Specific Value Range in Google Sheets

Here you should not use the ISTEXT or ISNUMBER function. Just a comparison operator can solve the dilemma. How? See one example formula.

=ArrayFormula(if(B3:E8<10,B3:E8,))

This formula will only copy the numbers in the range B3: E8 that less than 10. But there is a twist.

You can’t use the “>” comparison operator in the same way. The reason, this time the number will be copied correctly, but along with the text in the range too.

Because the ISNUMBER function may return TRUE for text also when using “>” comparison operator. To avoid that, the formula should be adjusted as below.

=ArrayFormula(if(isnumber(if(B3:E8>10,B3:E8,)),B3:E8,))

That’s all.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.