Need help on replace all function

[mahesh8498]

Hi All,

I have 1000 country codes in column B. Before preparing final data i m everytime manually replacing country code with country name by using REPLACE ALL function.

For example : -

I have country code like US, AU, CA, IN, NZ in coulumn B and i changed US to unites states, AU to australia manually everytime.

Is there any easy way where i can replace all codes with country name at a one time???

Thanks in advance.

 

[kpark91]

You can use a macro to achieve what you want to do

Sub ReplaceAllCountryCodes()
    Dim LR&, i&
    LR = Range("B" & Rows.count).End(xlUp).Row 'Assumes your country codes are only in column B
 
    'Change 1 to your starting row whether it be 2 or 3
    For i = 1 To LR
        Range("B" & i).Value = Replace$(Range("B" & i).Value, "AU", "Australia",,,vbBinaryCompare)
        Range("B" & i).Value = Replace$(Range("B" & i).Value, "US", "United States",,,vbBinaryCompare)
        'Add more country codes in the same manner
    Next i
End Sub

 

[mahesh8498]

How to edit this macro for multiple column? As i have data from c to w column. and getting an error message while edditing macro. macro is allowing enter range only for 1 coloumn