Delete everything except last 9 digits

**McTwizle** · 02-06-2015, 11:48 AM

I am pulling information out of a database and pasting it in Excel (Office Pro Plus 2010). I get 6 columns of info. I need to get 2 of the columns formatted to paste on another sheet. 1 column has part numbers formatted like this :

...1....123456789
......2....123456789
.........3....123456789

I need to delete everything except the last 9 digits all the way down the column so I can copy and paste a neat column with just the last 9 digits. I just went through ~1000 cells deleting the ...1.... and ......2.... etc. My fingers hurt. I have to do this 10,000 more times. Is there a better way to automate this process ?

Very new to Excel.......please be nice.

**mcmahobt** · 02-06-2015, 11:52 AM

You can use RIGHT() to pull a determined amount of characters from a string, and use those number values later. It would look something like this (assuming your data starts in A1)

Please Login or Register  to view this content.

Drag down to fill.

You can then copy/paste those values over. Will that work?

**Pete_UK** · 02-06-2015, 11:55 AM

Put this formula in a helper column (e.g. in G1 if your data occupies 6 columns and the data starts in cell A1):

=RIGHT(A1,9)

then copy down. A quick way of doing this is to double-click the fill handle (the small black square in the bottom right corner of the cursor, with G1 selected). If you want those digits to be proper numbers, then make the formula:

=RIGHT(A1,9)*1

Then you can fix the values (using <copy>, <Paste special> | Values | OK | <Esc> ) then you can copy and paste those values to over-write the values in column A, and then you can delete column G.

Hope this helps.

Pete

**McTwizle** · 02-06-2015, 12:22 PM

Perfect. This works exactly as I need it to. Thanks for the quick replies and the help. My fingers will live a little longer now. Have a great weekend !