CODEWITHSUNDEEP
excelreplaceworksheet-function
Paolo Bernasconi
Paolo Bernasconi

Reputation: 2030

Replacing Text in excel fields

I'm trying to change the acronyms for several countries into their full names. The column of two letter acronyms that I want to replace looks like this:

HU
DE
DE
HU
PL
UA
CZ
SK
BY
EE
MD
SP

And in a separate sheet, I have a list of countries that correlate to the acronyms, which I wanted to use as the reference to speed up the process of replacing the acronyms to full names, like this:

A   Austria
BY  Belarus
BG  Bulgaria
HR  Croatia
CZ  Czech
DK  Denmark
EE  Estonia
GR  Greece
HU  Hungary
IE  Ireland
IT  Italy
LV  Latvia
LT  Lithuania
LX  Luxemburg
NL  Nederlands
PL  Poland
RO  Romania
RU  Russia
CS  Serbia
SK  Slovakia
UA  Ukraine
UK  United Kingdom

Using an IF and then a REPLACE formula, I wanted to replace the acronym, such as HU with Hungary. I was trying to do this with a formula which looked like this: 

=IF(FR_HU_Consolidation!C2= B21, REPLACE(B22,1,100,D22), nothing)

in which, FR_UH...C2 is the first cell in the first sheet, in which I want to replace the HU acronym in sheet 1.

My task is to automate this process for a list of 100's of countries acronyms.

My idea was to use an IF statement to see if (sheet 1 acronym) == (sheet 2 (reference) acronym), then if it is true, replace the acronym with the respective country, else do nothing.

I'm not sure if this task is too complicated for excel, but if it is possible, it would save me a lot of time.

Thanks in advance for the help.

Upvotes: 0

Views: 2152

Answers (1)

Alex P
Alex P

Reputation: 12497

A simple way would be to use a VLOOKUP. Suppose I have a list of acronyms in Sheet1 on column A and a list of acronyms in Sheet2 in columns A and B:

    Sheet 1                 Sheet 2
     A    B                 A     B
1    HU   =VLOOKUP(...      A     Austria
2    DE                     BY    Belarus
3    DE                     BG    Bulgaria
4    HU                     HR    Croatia
5    etc.

In column B of Sheet1 I would then have the following formula:

=VLOOKUP(A1, $Sheet2!$A$1:$B$4, 2, 0) //change range references as appropriate

Then all you need to do is drag the formala down the list of acronyms in Sheet1 and copy and paste the results in column B into column A on Sheet1.

Hope this helps get you started.

Upvotes: 1

Related Questions