Find & Replace 3,000+ Values

[sbaker333]

Hello,

I'm not sure if this will require a macro, or if there is a formula that could resolve this, but I'm looking to find and replace a large number of values.

In Column A of Worksheet 1, I have a series of IDs that look something like this....

Current IDs (Column A)
17775970,6213054,6211374
3743032,3743272
3804472
4120715 and (8875570,420386,420026,438866,427106)
(4121915,4121795) and (9143290,6254694)

Then, in Worksheet 2, I have a mapping file that links all "Old IDs" to "New IDs," like this....

Old ID (Column A)___New ID (Column B)
17775970___________283338
9621970____________283302
10951690___________283412
9231490____________102419
17564290___________283455
9288850____________102950
16696810___________283340
9288730____________102949

Some of the rows in Column A of Worksheet 1 ("Current Keys") contain values that need to be replaced. Some of them don't. I'd like to do two things:

1) Identify which rows under "Current IDs" contain old IDs.
2) In Column B of Worksheet 1, I'd like to have an updated version of the rows, where all old IDs have been replaced with new IDs, but all other values remain the same.

If there were only 1 ID per row in Worksheet 1, I could do a simple VLOOKUP. But the problem is that there are multiple IDs per row, and they're mixed up among other values (parentheses, commas, etc.).

Please let me know if you have any ideas on the best way to solve this.

Thanks,
Scott

[natefarm]

The following code will parse the numbers out of column A and put them in column B. You could then do Vlookups on column B to get your new IDs.
Let me know how it works.

Please Login or Register  to view this content.