Wildcards in IF statement

  • Thread starter Thread starter Fred Smith
  • Start date Start date
F

Fred Smith

I need to select base on whether a field has three alphabetic characters
followed by three numbers. I tried

=IF(a1="???###",1,2)

but it didn't treat my string as a wildcard. Is there another way to do it?
 
without code, it's a bit of a monsterformula but there are 2 ways of writing it. Either create a table as follows
0
48
58
65
91
97
123

name it myRange and use
=IF(AND(VLOOKUP(CODE(LEFT($E$1,1)),myRange,2,TRUE)="A",VLOOKUP(CODE(MID($E$1,2,1)),myRange,2,TRUE)="A",VLOOKUP(CODE(MID($E$1,3,1)),myRange,2,TRUE)="A",VLOOKUP(CODE(MID($E$1,4,1)),myRange,2,TRUE)="N",VLOOKUP(CODE(MID($E$1,5,1)),myRange,2,TRUE)="N",VLOOKUP(CODE(RIGHT($E$1,1)),myRange,2,TRUE)="N"),1,2

OR, without a table

=IF(AND(VLOOKUP(CODE(LEFT($E$1,1)),{0,"E";48,"N";58,"E";65,"A";91,"E";97,"A";123,"E"},2,TRUE)="A",VLOOKUP(CODE(MID($E$1,2,1)),{0,"E";48,"N";58,"E";65,"A";91,"E";97,"A";123,"E"},2,TRUE)="A",VLOOKUP(CODE(MID($E$1,3,1)),{0,"E";48,"N";58,"E";65,"A";91,"E";97,"A";123,"E"},2,TRUE)="A",VLOOKUP(CODE(MID($E$1,4,1)),{0,"E";48,"N";58,"E";65,"A";91,"E";97,"A";123,"E"},2,TRUE)="N",VLOOKUP(CODE(MID($E$1,5,1)),{0,"E";48,"N";58,"E";65,"A";91,"E";97,"A";123,"E"},2,TRUE)="N",VLOOKUP(CODE(RIGHT($E$1,1)),{0,"E";48,"N";58,"E";65,"A";91,"E";97,"A";123,"E"},2,TRUE)="N"),1,2)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Use of Wildcard characters with replace 2
Wildcards in IF statement 10
How can I use wildcards in IF statements and Macros 2
Excel Wildcard in Excel array formula 0
How do I search without wildcards? 8
Why not accept wildcards for REPLACE as well as FIND ? 5
Replacing using a wildcard 2
Wildcard Character in IF formulas 2

Back
Top