Replacing short strings

[jamieb]

Hi,

I have a long list of locations in an excel spreadsheet, stating either the country (eg India) or for US locations the state (in a two letter format, eg NY). I need to change all the state location to say "USA". How can I do this, without doing 50 find and replaces?

Can I do something based around the fact all the USA locations are two letters, whereas all the others are longer?

Apologies if this has been asked before, if so could you point me in the right direction.

Thanks,
Jamie

[oldchippy]

Hi,

May be something like this will help?

=IF(LEN(A1)=2,"USA",A1) copy down

[olasa]

One option is:

Column A is the old list
Column B is where this formula goes (new list)
Column C is the state list
=IF(ISNUMBER(MATCH(A1,$C$1:$C$53,0)),"USA",A1)

HTH
Ola

[jamieb]

brilliant, thanks.

Sorry for asking such seemingly obvious questions, but I'm still learning...

[Ron Coderre]

try this (as you'll see....much easier to do than to describe):

Please Login or Register  to view this content.

Is that something you can work with?