Assigning a numerical value based on one of six text strings

G'day,

Let's say the cell E4 contains some text. The text can be a range of things, i.e. "DIFFERENT_500Hz_5percent_UP" or "SAME_500Hz" or a whole bunch of permutations.

I want to create a formula that does the following:

• If E4 has the string "SAME" in it, return a value of 0.
• If E4 has the string "_1percent" in it, return a value of 1.
• If E4 has the string "_2pt5percent" in it, return a value of 2.
• If E4 has the string "_5percent_" in it, return a value of 3.
• If E4 has the string "_10percent_" in it, return a value of 4.
• If E4 has the string "_20percent_" in it, return a value of 5.

For instance, if E4 was "DIFFERENT_500Hz_5percent_UP", the value returned would be "3" because it contained the string "_5percent_". Alternatively, if E4 was "SAME_500Hz", it would return "0" since it contained the string "SAME".

So basically E4 can be in one of six states, and I want Excel to tell me which one it's in.

I'd appreciate any help.

Cheers,

Dougal

Dougal

Try this array entered (ctrl, shift, enter) formula

=MATCH(TRUE,ISNUMBER(SEARCH({"same","_1percent","_2pt5percent","_5percent_","_10percent_","_20percent_"},E4)),0)-1

If there is no match, it will return the #N/A error.


rylo

Thanks Rylo!

After deleting the space in "_20percen t_", it works a dream.

Now to understand it....

Thanks again for your help.

Cheers,

Dougal