0

I have a spreadsheet that lists geographic regions and unique ID numbers in a similar format to this:

image

I would like to remove all the ID numbers and have the region names remain, but currently I must either type each ID number into the find/replace tool, or select each cell individually and manually remove the ID number.

Is it possible to use Excel's find and replace tool, or an Excel formula, to batch remove all ID numbers?

1
  • You can use a simple MID function to extract only region part from your cells to adjacent cells e.g. =MID(A1,12,LEN(A1)) Assuming that in all cells the Region starts at 12th character. Then just do a copy-paste special values in whichever column or the same original column.
    – patkim
    Feb 10 '17 at 3:21
1

You can use a formula like this:

=RIGHT(A1,LEN(A1)-11)

This brings back the characters from the right side of cell A1 until the last 11 characters.

1
  • Yes, this works very well. I'd tried the RIGHT function before but hadn't realised you could use the LEN formula to delete characters from the front of the cell. Thanks! Feb 10 '17 at 3:58
-1

you can create a VBA script that compare 2 rows at the time

111, aaa

111, aaa

222, ccc

and if they are the same delete the id of the first row

     ,aaa

111, aaa

222, ccc

keep doing that until you reach the end, just sort them first.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.