How do I delete 100 account numbers from a spreadsheet colume wer.

N

Ndubuisi Azuogu

How do I delete 100 account numbers from a spreadsheet colume were it only
leaves the last 5 digits of all the accounts with out having to physicaly
delete each account manually
 
J

JulieD

Hi

use a helper column, in a column to the left of your numbers put in
(assuming your numbers start in A1)
=RIGHT(A1,5)
fill down as needed (move mouse over bottom right corner of the cell until
you see a + then double click),
(once you're happy with the result)
select column B and copy it
click on A1 and choose Edit / paste special - values and click OK
now the account numbers will have been replaced with the last 5 digits of
the account numbers and you can delete column B.

Hope this helps
Cheers
JulieD
 
N

Nick Hodge

Ndubuisi

If the account number is in say A1, then in a spare cell to the right type

=RIGHT(A1,5)

and enter, now copy down the 100 rows and copy this new account number,
select the old account numbers and Edit>paste special...Values the new ones
over the top of the old. Now delete the column you used to create the new
numbers

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
Top