Modify an unknown range of cells

[timmy2uk]

hey all, been trying this for a while...i have a list of countries in a column and have to go through this column ensuring they are in the correct format. I'm struggling to figure out how to iterate through each row of the column, test it's value and then change if necessary...

for example
column a --> column A(modified)
Usa --> USA
Ukraine - Mobile (Umc) --> Ukraine - Mobile (UMC)
Uk --> UK
Uruguay --> Uruguay

so values in column a becomes like those shown above. any help would be greatly appreciated

cheers
timmy

 

[timmy2uk]

But, I truly think that vlookup might work out better for you

Basically you would make a table that in column 1 contained the possible entries that you would need to change, column 2 would contain what you want the data in column 1 changed to. Probably naming that table then use this as your formula.

=Vlookup(A2, Lookuptable, 2, 0)
dragging down as needed.

that works great...but just one thing...if a value doesn't change N/A appears in the cell, is there anyway to get it so that it the name appears.
For example,
With "Afghanistan" there's no change to be made, but "n/a" appears. anyway around this so that "Afghanistan" appears?

 

[Boller]

timmy2uk

You're not going to get anywhere with this unless you answer Norie's initial basic questions.

 

[Norie]

You can wrap the VLOOKUP in ISNA.

=IF(ISNA(VLOOKUP(A1, TableRange, 2, 0)), A1, VLOOKUP(A1, TableRange, 2, 0))

Obviously use your VLOOKUP and references.

 

[timmy2uk]

You can wrap the VLOOKUP in ISNA.

=IF(ISNA(VLOOKUP(A1, TableRange, 2, 0)), A1, VLOOKUP(A1, TableRange, 2, 0))

Obviously use your VLOOKUP and references.

cheers, that worked perfectly =)

thanks to everyone for their help, it is greatly appreciated