Wildcard

[kym.wong@yahoo.com]

Dear all,


how to shorten this formula?


=IF(OR(RIGHT(V2,1)="1",RIGHT(V2,2)="1N",RIGHT(V2,2)="AP",RIGHT(V2,2)="SH",RIGHT(V2,2)="MG"),1,2)


As you see, I am trying to put a value of 1 to any stock ID where the last 2 count from the right are any alphabets. There are many stock IDs with different alphabets and I can't possibly list them down in the formula. How do I do it? Is there a wildcard that I can add?


thanks

 

[NARAYANK991]

Hi Wong ,


Can you list all the valid stock IDs in a range of cells ? If so , then you can check that the data in V2 matches any stock ID from the list of valid stock IDs.


Narayan

 

[kym.wong@yahoo.com]

Thanks Narayan. But that's what I don't want to do. It's a very long list. There must be a way to put a wildcard so that any stock ID that ends with an alphabet can be changed to value 1.


Any other ways?

 

[kym.wong@yahoo.com]

Alternatively, I tried to group all the stock ID that has alphabets as below


=IF(OR(RIGHT(V2,1)="1",RIGHT(V2,2)="AP","SH","MG","etc",etc),1,2)


That didn't work too.

 

[NARAYANK991]

Hi Wong ,


In the formula that you have given , even "1" is accepted , even though this does not have any alphabet in it ; secondly , using wildcards will mean that any alpha entry , even something like "ZZ" will be accepted ; is this OK with you ?


Secondly , I think there is some misunderstanding here ; your list can be very long ; you do not have to specify it in the formula ; you can just give the range of cells that it is present in e.g. G7:G102.


Narayan

 

[Colin Legg]

Your alternative formula didn't work because the letter pairings need to be in an array constant. For example:

[pre]

=IF(OR(RIGHT(V2,1)="1",OR(RIGHT(V2,2)={"AP","SH","MG"})),1,2)
But as Narayank991 points out, it'd be much better to put these two letter combinations in a look up table.


So, for example, put the values AP SH and MG in cells X1:X3, then your formula becomes:

=IF(OR(RIGHT(V2,1)="1",ISNUMBER(MATCH(RIGHT(V2,2),$X$1:$X$3,0))),1,2)

[/pre]
From there, you can add more letter combinations to your list and the only adjustment you have to make to your formula is to make sure that $X$1:$X$3 is changed to reference the entire lookup table. The advantages of this are:

(1) Your formula doesn't get any longer as more letters are added

(2) It's easy to see in the look up table which letter combinations there are

(3) Maintenance of the letter combinations is easier


From there, you can even improve the formula again by making the reference to the look up table dynamic, so it automatically expands with the data. The advantage of this is that you'd never have to amend the formula!