Bulk change numbers?

[rive22]

How do you change multiple groups of numbers across multiple files.

For ex; I imported 52 worksheets into excel and lets just say each sheet has about 1000 rows with 20 columns. Half of the sheets are full of index numbers and the other half are full of items that reference those numbers.

I need to change about 2000 numbers globally across each of the worksheets like this

5102 --> 25102
5103 --> 25103
5104 --> 25104

With ASAP utilities I can change the numbers globally across each sheet, but as far as I can tell I've only found the option to do it one number a time. If I create a dictionary of what all the number should be changed to, is there a way to batch change them all? With any kind of script or software it doesn't matter.

 

[PTNL]

Just to name a few options...

- You can write a VBA macro in Excel.

- If you're more comfortable in another language and have a small amount of SQL knowledge, then you can execute SQL statements against an Excel file. The parsing logic would be written in whatever language you are comfortable with, and you'd just use an ODBC library to connect to the file.

- There are external connector libraries that can (more natively) work with the Excel file; POI and NPOI come to mind.

- The Office Interop libraries can be used. Though this does rely on the machine running the application to have the same version of Office installed for the interops to work.

 

[rive22]

One key part I forgot to mention, I can only change the numbers in the first column of each sheet, the rest of the columns and their numbers must remain unaffected from this.

Which option would you say is the way I should explore then?

 

[Demon10000]

I'm no excel guru, so there is probably a better way to do it ...

I would write a VBA macro that looped through every cell in the first column and then added 2000 to it (or whatever logic you wanted applied).

A tip -- turn screen updating off before it started to run, and turn it back on just before it's done. That will speed it up considerably. And if it crashes midway through, remember that you turned it off because excel will stop updating cells automatically until you turn it back on.