Asterisk in Excel

[lstewart]

I am using the following formula
=IF(MATCH(Z6,A$219:A$243,0) < MATCH(AB6,A$219:A$243,0),"Yes", "No")

The match is using letters with symbols e.g B-, B, B+

All works fine, until I use the * symbol.

I know to use the ~ symbol in Excel when * is in the spread sheet however the formula above does not recognise I have * in the match

Excel ignores the *

When I do a find and replace in Excel for A* I use the A~* and that works fine, just my formula that ignores the *.

 

[Andrew Poulsom]

Does this work for you?

=MATCH(SUBSTITUTE(Z6,"*","~*"),A$219:A$243,0)

 

[lstewart]

Does this work for you?

=MATCH(SUBSTITUTE(Z6,"*","~*"),A$219:A$243,0)

This works great, thank you.

If I have A*- and A*+ will I have to do a substitute each time. I thought the "*" would substitute even if it was A*+

 

[lstewart]

Excel gives me a #Value! on my formula below, I can't work out why The text entered is A*+ and B.

=IF(MATCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(AA2,"A*","A~*",),"A*-","A~*-"),"A*+","A~*+"), A$219:A$243,0) < MATCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(AB2,"A*","A~*",),"A*-","A~*-"),"A*+","A~*+"), A$219:A$243,0),"Yes", "No")

 

[Andrew Poulsom]

You only need to replace the * with ~*, so try:

=IF(MATCH(SUBSTITUTE(AA2,"*","~*"),A$219:A$243,0)<MATCH(SUBSTITUTE(AB2,"*","~*"),A$219:A$243,0),"Yes","No")

 

[lstewart]

You only need to replace the * with ~*, so try:

=IF(MATCH(SUBSTITUTE(AA2,"*","~*"),A$219:A$243,0)<match(substitute(ab2,"*","~*"),a$219:a$243,0),"yes","no")[ quote]

</match(substitute(ab2,"*","~*"),a$219:a$243,0),"yes","no")[>

 

[lstewart]

I understand now, I thought I needed to substitute each value.

It works with the simplified formula

=IF(MATCH(SUBSTITUTE(GD2,"*","~*"),A$219:A$243,0) < MATCH(SUBSTITUTE(GF2,"*","~*"),A$219:A$243,0),"Yes", "No")

Thank you.