Move partial number from A1 to B1 then delete A1

2 posts / 0 new
Last post
Move partial number from A1 to B1 then delete A1

Hi, I'm trying to get the last 4 digits of 800 numbers into a new column. All numbers start in cell A1 and go down the column. The number range in size from 9 digits down to 4 digits. I used the =RIGHT formula to move them to column B and that worked great, but now I want to be able to delete the old numbers and just be left with the 4 digits, and when I delete column A all the data I moved to column B disappears as well. Is there a way to change the new data so it doesn't relate to the old data?

Copy and Paste Values to remove formulas

The problem you are hitting here is that when you delete the cells with the source data for the formula, the formula no longer works since the data is gone.

The trick here is to:

  • copy the values in column B
  • select the first cell in column B
  • right-click the selected cell and choose Paste Special.

When you do that, you'll see a range of different options as shown below - you want the option for Values as shown.

Excel Paste Special dialog box with Paste Values selected.

This will paste the copied cells over the top of themselves with just the values from the formula - the formula itself will be discarded. You can then delete column A.

Add new comment