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.

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.