Possible? Trying to do a wild card search...

I want to set up a if statement without having to write a long OR statement capturing all possible values. Here's an example of what I would like to do...

Say if I have a bunch of different values including...

Eastern Europe
Western Europe
Central Europe

I would like to set up a if statement in a seperate column that would search within this column for anything that contains Europe (*Europe*) and replace it with "Europe". Text may not be at the end of the string

Originally Posted by andyd2k

I want to set up a if statement without having to write a long OR statement capturing all possible values. Here's an example of what I would like to do...

Say if I have a bunch of different values including...

Eastern Europe
Western Europe
Central Europe

I would like to set up a if statement in a seperate column that would search within this column for anything that contains Europe (*Europe*) and replace it with "Europe". Text may not be at the end of the string

Hi andyd2k,

Try this formula

=IF(A6="Europe","Europe",RIGHT(A6,LEN(A6)-FIND(" ",A6)))

when you say "Text may not be at the end of the string", I'm assuming you just have Europe in the cell?

oldchippy

Originally Posted by andyd2k

I want to set up a if statement without having to write a long OR statement capturing all possible values. Here's an example of what I would like to do...

Say if I have a bunch of different values including...

Eastern Europe
Western Europe
Central Europe

I would like to set up a if statement in a seperate column that would search within this column for anything that contains Europe (*Europe*) and replace it with "Europe". Text may not be at the end of the string

here is a trick
copy this column to another column.
select that new column
press Ctrl+H
in Find what: type *Europe* (with * at both ends of Europe)
in Replace with: type Europe
press Replace All.

hope this would do what you have asked for.

Originally Posted by oldchippy

Hi andyd2k,

Try this formula

=IF(A6="Europe","Europe",RIGHT(A6,LEN(A6)-FIND(" ",A6)))

when you say "Text may not be at the end of the string", I'm assuming you just have Europe in the cell?

oldchippy

I meant that the word may be anywhere in the string (i.e. "Europe" may be at the start or middle of the string)

Starguy, thanks for the tip. Didn't know you can do that. Won't work for this though as I'm trying to automate something as much as possible for something that I colleague and I work on weekly