Macro for find/replace specific text within a column

Hi everyone.
I have a column B with 726 book titles. I need to find the ones that have country codes in brackets, eg. (ES) and replace the text inside the brackets with a full country name, e.g. (Spain). I came across the macro below. It seems to work, but the problem is, it would only work if I had just (ES) in my cell and nothing else. I need a code that will replace this (ES) for (Spain) and other country names but the book title will remain. For example if the title of a book is: "Workbook Second Edition 1 (PL)" I would like to change it to "Workbook Second Edition 1 (Poland)".

In Sheet 2, I have created a column A with a full list of country codes in brackets and column B with a full list of the corresponding country names in brackets. These are the values that the macro should look for in my Column with book titles (in sheet 1).

Could anyone please help me modify this macro or suggest a new one in order to make it work?

Please Login or Register  to view this content.

Thank you in advance.

Hi, welcome to the forum.
You could either use the Application.Match function or write a find routine.

Please Login or Register  to view this content.

You will have to modify your code a little but the Function will return the countryname to the corresponding value you pass as parameter, the values must exist in Sheet2