Contextures

Home > Data > Data Entry > Paste Numbers

Excel Copy Paste Number Cells Only

See 3 different ways that you can select and copy the numbers only, in an Excel column. Then, paste the copied numbers in a different location, in a consecutive block of cells.

copy and paste number cells only

Video: Copy and Paste Numbers Only

If a range of cells contains a mix of number cells, blank cells and text cells, you can use built-in Excel tools to select just the cells with numbers. Then, copy and paste the cells to another location on the worksheet, in a solid block of cells.

copy and paste number cells only

Watch this short video, to 3 ways to copy and paste just the numbers in an Excel column. Written steps and timeline are below the video.

Note: To follow along with the video, you can get the sample file, in the download section at the bottom of the page.

Video Timeline

  • 0:00 Copy and Paste Number Cells Only
  • 0:43 Ignore Text Cells When Selecting
  • 1:23 Select Formula Cells With Numbers

How to Copy and Paste Numbers Only

To select just the number cells in an Excel column, you can use one of the following techniques:

-- 1) Copy and Paste Typed Number Cells Only

-- 2) Ignore Text Cells When Selecting

-- 3) Select Formula Cells With Numbers

The steps for all three are shown in the video above, and in the written steps below.

go to special formulas

1) Copy and Paste Typed Number Cells Only

In this example we've got cells where there are numbers typed, and several cells are blank. None of the cells have formulas.

The goal is to copy just the numbers, and paste them in a consecutive block of cells, somewhere else on the worksheet.

Select Numbers Only

To begin to copy and paste just the numbers, follow these steps:

  • First, select cells B2:B11, where we want to copy just the numbers
  • Next, on the Excel Ribbon, go to the Home tab
  • Then, at the right end of the tab, click the Find and Select command
  • Next, click the Constants command.

Now just the number cells are selected -- none of the blank cells are included.

find and select constants

Copy and Paste

Next, follow these steps, to complete the copy and paste steps:

  • To copy the selected cells:
    • use the keyboard shortcut - Ctrl + C
    • OR, on the Excel Ribbon's Home tab, click the Copy command
  • Next, click the cell where you want to paste the numbers
  • Finally, to paste the selected cells:
    • use the keyboard shortcut - Ctrl + V
    • OR, on the Excel Ribbon's Home tab, click the Paste command

The numbers all appear in their original sequence with no blanks between them.

copy and paste number cells only

2) Ignore Text Cells When Selecting

In this example we've got cells where there are numbers typed, some cells have text entries, and several cells are blank. None of the cells have formulas.

The goal is to copy just the numbers, and paste them in a consecutive block of cells, somewhere else on the worksheet.

Select Numbers Only

To begin to copy and paste just the numbers, follow these steps:

  • First, select cells B2:B11, where we want to copy just the numbers
  • Next, on the Excel Ribbon, go to the Home tab
  • Then, at the right end of the tab, click the Find and Select command
  • Next, click the Go To Special command.

find and select Go To Specail

Go To Special Dialog Box

The Go To Special dialog box opens, where there are many options to choose from.

  • First, click the option for Constants
  • Next, in the check boxes:
  • Leave the check mark for Numbers
  • Remove the check marks for Text, Logicals and Errors
  • Next, click OK, to apply the selection, and to close the dialog box

On the worksheet, only the number cells are selected. None of the text cells or blank cells are included.

select Constants in Go To Specail dialog box

Copy and Paste

Next, follow these steps, to complete the copy and paste steps:

  • To copy the selected cells:
    • use the keyboard shortcut - Ctrl + C
    • OR, on the Excel Ribbon's Home tab, click the Copy command
  • Next, click the cell where you want to paste the numbers
  • Finally, to paste the selected cells:
    • use the keyboard shortcut - Ctrl + V
    • OR, on the Excel Ribbon's Home tab, click the Paste command

The numbers all appear in their original sequence with no blanks between them.

3) Select Formula Cells With Numbers

In this example there are cells where numbers and empty strings are returned by a formula.

=IF([@Amt]="","",[@Amt]*[@Price])

The goal is to copy just the numbers, and paste them in a consecutive block of cells, somewhere else on the worksheet.

Select Numbers Only

To begin to copy and paste just the numbers, follow these steps:

  • First, select cells B2:B11, where we want to copy just the numbers
  • Next, on the Excel Ribbon, go to the Home tab
  • Then, at the right end of the tab, click the Find and Select command
  • Next, click the Go To Special command.

find and select Go To Specail

Go To Special Dialog Box

The Go To Special dialog box opens, where there are many options to choose from.

  • First, click the option for Formulas
  • Next, in the check boxes:
  • Leave the check mark for Numbers
  • Remove the check marks for Text, Logicals and Errors
  • Next, click OK, to apply the selection, and to close the dialog box

On the worksheet, only the number cells are selected. None of the text cells or blank cells are included.

select Formulas in Go To Specail dialog box

Copy and Paste

Next, follow these steps, to complete the copy and paste steps:

  • To copy the selected cells:
    • use the keyboard shortcut - Ctrl + C
    • OR, on the Excel Ribbon's Home tab, click the Copy command
  • Next, click the cell where you want to paste the numbers
  • Finally, to paste the selected cells:
    • use the keyboard shortcut - Ctrl + V
    • OR, on the Excel Ribbon's Home tab, click the Paste command

The numbers all appear in their original sequence with no blanks between them.

Download the Sample File

Paste Numbers: Get the sample file for copying and pasting number cells only, to follow along with the video. The zipped file is in xlsx format, and does not contain any macros.

____________________

Video Transcript

Today I'm going to show you how to select cells where there are blanks and numbers or text and copy and paste, just the numbers.

Copy and Paste Number Cells Only

In this example we've got cells where there are numbers typed. There's no formula, just numbers.

  • I'm going to select these 10 cells where the numbers and blanks are
  • And to select just those numbers, on the Home tab, go over to Find and Select and click, and then select Constants.

Now just the number cells are selected.

  • We can copy, and click where you want to paste and click the paste button.

The numbers all appear in sequence with no blanks between them.

Ignore Text Cells When Selecting

On the next sheet, I've got numbers and some text, so I'm going to do things a little differently, so I select just the numbers.

  • I'll click at the top of the column and that selects the whole column.
  • Then I'll go to Find and Select and click Go To Special.

In this window, we've got lots of options.

  • We want to select Constants. I don't want Text or Logicals or Errors.
  • I'll click OK, and now just the numbers are selected. It ignored any text in that column.

Now I can copy, select a cell and paste.

Select Formula Cells With Numbers

On the third sheet we've got a price here.

And then in this column there's a formula.

It's saying, IF the amount cell is empty, just put an empty string here, otherwise, multiply the amount by the price.

There's a formula in each of these cells.

  • I'm going to select this column, so I'll go back to Find and Select, Go To Special.
  • And in this case I want formulas, and I'll uncheck the ones I don't want.
  • I just want to leave numbers checked.
  • I'll uncheck the other three options.
  • Click OK, and now just those numbers are selected

I can copy them, click somewhere and paste.

____________________

More Copy & Paste Tutorials

Paste, Skip Blanks

Fix Copy & Paste Problems

Copy/Paste in Filtered List

Increase Numbers With Paste Special

 

 

About Debra

 

Last updated: February 17, 2024 2:37 PM