Mass find and replace of unique identifiers

Hi

I've inherited a massive dataset that contains many unique identifiers. The unique identifiers are too long for my purposes, and I need to shorten them across the database. Currently unique identifiers are 23 characters long (eg: @I6000000002997995944@), and I need to replace them with identifiers that are 8 characters long (eg @I10001@)

In column A and column C of the database contain references to the 23-character unique identifiers (as well as other content) - these are the columns I need to have content replaced in. I have a list of all 23-character unique identifiers at column G, and the corresponding 8-character unique identifiers in column H.

Basically, I want to know what is the best way to search for values in column G that are contained in column A and column C, and where found, replace them with the corresponding value in column H.

The problem is the database is very big. Column A and C have more than 200,000 values, and the number of unique identifiers is 75,000. Doing a search and replace one by one is not possible.

Sorry if I wasn't clear enough in explaining the issue.

Thanks

Hi
Try - Ctrl+H - insert the values and click " Replace all"

Welcome to the forum.

You say that there is other content in the cells of columns A and C that includes the 23 character codes. Seeing that you have 75000 replacement codes, search and replace would be a very tedious and time consuming task. Please upload a good sampling of your data so that a method of deconstructing the cell contents of each cell in columns A and C to isolate the 23 character string can be accomplished. Once the 23 character string has been isolated in a column of its own for both column A and C then a VLOOKUP or INDEX MATCH formula can be used to find the replacement string and then the cells can be reconstituted with the replacement values.

Instructions for uploading:
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic