I used a text file with your three examples, and a second file, an XLSX, with the three "OLD IP ADDRESSES" in a different order than above in its column C. The "NEW IP ADDRESSES" were in column E and matched the order of column C.
I chose to let them match that order since you give pretty sufficient information and it seems unlikely you would not have them in the same order and not mention that fact. I also don't see how it could work if that were so, so... there's that too!
The XLSX file I used is called "Book 3.xlxs" and obviously, you would edit that to fit your name. The text file gets opened and immediately converted to an Excel file. (Presumably you can successfully open it as an Excel file. And then save it back into standard text file format or the .TXT format you find it in.) However, you can do it in a more complicated way if necessary. Due to the MANY possibilities, I shall not delve into that.
The formula I used is:
=SUBSTITUTE(A1, INDEX([Book3]Sheet1!$C$2:$C$4,XMATCH(1000000,SEARCH([Book3]Sheet1!$C$2:$C$4,A1,1),-1),1), INDEX([Book3]Sheet1!$E$2:$E$4,XMATCH(1000000,SEARCH([Book3]Sheet1!$C$2:$C$4,A1,1),-1),1))
There are three basic sections: 1) The SUBSTITUTE
that does the final work, 2) The first INDEX/XMATCH
that finds the OLDIPADDRESS in the source data, and 3) The INDEX/XMATCH
that finds the new one for each line's source data.
The INDEX/XMATCH
is in each case, a standard INDEX/MATCH
style construction. The important part is the SEARCH
inside them. SEARCH
is used to test the text data for any given line against ALL the Old IP Addresses. ONE of them will give a number, the rest an ERROR. XMATCH
(not MATCH
as it cannot do this) is used to seek the number 1,000,000 which will be greater than any value SEARCH
can yield since there is a limit to how many characters an Excel cell can contain. The match type is "Exact, or next smaller" so it finds the only numerical result. That result is the row of the column C data that the Old IP Address in the line being checked's data cell matches.
And then the other INDEX/XMATCH
does the same to find the corresponding row of the New IP Addresses.
Those two parts operate independently of each other, which is usually a bad idea if one use one to feed the other, but in this case the only drawback is that an edit, to upgrade it perhaps, needs to be done equally to the other clause. For example, the ranges must stay synchronized.
Finally, the SUBSTITUTE
comes into the fray replacing the old address with the new address.
At this point, you have a new column in which all the old addresses have been replaced by the new addresses.
As mentioned, you seem to have provided a complete set of information, so I have provided nothing in the way of "if error, do this"... And couldn't really, without more information. Since there is no reason an address cannot exist many times in the text file, its greatly larger number of rows than in the XLSX file is neither unlikely nor unreasonable. However, if there will or could be TXT file data that will not match up with the new IP information, you would want to provide a path for errors due to no match to carry the original data from those rows through to the new column.
Finally, you would copy the new column and paste it as values (Paste|Special|Values
) overtop the original column. To avoid difficulties (complexities) with the TXT file, create the replacement column in the XLSX file. And, if need be, simply close it afterward without saving.