Is there a formula that can pattern match?

  • Thread starter Thread starter Guest
  • Start date Start date
Dave,

Thanks for the great observation. Unfortunately on my sheet your formula
returns FALSE for good combinations too even though entered as an array
formula.

Here is a version of my code that takes care of the scientific notation:

=(COUNTIF(A1,"???.???.????.??????.???.??.???")*(ISNUMBER(VALUE(SUBSTITUTE(A1,".",""))))*(LEN(TEXT(SUBSTITUTE(A1,".",""),"#######################0"))=24))=1

Cheers,
KL
 
Sorry for being so insisting, but I hate giving in with this one. My last
formula had a flaw too. I have searched the web and run into an interesting
solution by Aladin Akyurek here
http://groups.google.es/groups?hl=es&lr=&[email protected]

I didn't realise you could return arrays via SUBSTITUTE. So I tried to use
this circumstance and here is my latest formula (non-array):

=((NOT(ISERROR(SUMPRODUCT(--SUBSTITUTE(SUBSTITUTE(UPPER(SUBSTITUTE(A7,".","")),"E","EE"),{0,1,2,3,4,5,6,7,8,9},0)))))*(COUNTIF(A7,"???.???.????.??????.???.??.???"))*(LEN(SUBSTITUTE(A7,".",""))=24))=1

The idea is as follows:

1) COUNTIF(A7,"???.???.????.??????.???.??.???")
ensures the pattern is correct

2) LEN(SUBSTITUTE(A7,".",""))=24
just in case the pattern is correct, but there is a "." instead of some
numbers.

3)
NOT(ISERROR(SUMPRODUCT(--SUBSTITUTE(SUBSTITUTE(UPPER(SUBSTITUTE(A7,".","")),"E","EE"),{0,1,2,3,4,5,6,7,8,9},0))))

this one takes advantage of the fact that SUMPRODUCT would return #VALUE! if
at least one of the array members, returned by --SUBSTITUTE, has a
Non-Number character (dots have already been removed).

As part of this one UPPER(SUBSTITUTE(A7,".","")),"E","EE") takes care of a
single "e" or "E" which in some positions is interpreted by Excel as a
scientific notation symbol. What this one does is just duplicate the "E" so
that SUMPRODUCT returns #VALUE!.

Am I missing something? Appreciate any expert input.

Thanks to everyone who contributed and best regards,
KL

KL said:
Dave,

Thanks for the great observation. Unfortunately on my sheet your formula
returns FALSE for good combinations too even though entered as an array
formula.

Here is a version of my code that takes care of the scientific notation:

=(COUNTIF(A1,"???.???.????.??????.???.??.???")*(ISNUMBER(VALUE(SUBSTITUTE(A1,".",""))))*(LEN(TEXT(SUBSTITUTE(A1,".",""),"#######################0"))=24))=1

Cheers,
KL
Click to expand...
 
Glad you found a better formula. But what did you type in to get False for the
good combination?

(Just curious--I'd go with the VBA approach <vbg>.)
 
Dave,

Now that I tried it again it works, but accepts
AAA.BBB.CCCC.DDDDDD.EEE.FF.GGG and 333.333.3333.333333.333.33.3e3 as a valid
combinations too.

By the way, here is my optimized formula:
=((NOT(ISERROR(--SUBSTITUTE(UPPER(SUBSTITUTE(A1,".","")),"E","EE"))))*(COUNTIF(A1,"???.???.????.??????.???.??.???"))*(LEN(SUBSTITUTE(SUBSTITUTE(A1,",",""),".",""))=24))=1

Cheers,
KL
 
Nice perseverance!

But both those returned false for me. (the 333. version returned true if I
didn't ctrl-shift-enter the formula, though.)




Dave,

Now that I tried it again it works, but accepts
AAA.BBB.CCCC.DDDDDD.EEE.FF.GGG and 333.333.3333.333333.333.33.3e3 as a valid
combinations too.

By the way, here is my optimized formula:
=((NOT(ISERROR(--SUBSTITUTE(UPPER(SUBSTITUTE(A1,".","")),"E","EE"))))*(COUNTIF(A1,"???.???.????.??????.???.??.???"))*(LEN(SUBSTITUTE(SUBSTITUTE(A1,",",""),".",""))=24))=1

Cheers,
KL
Click to expand...
 
No. But I don't think it's the OP (quartz) that's so persistent!

I think KL sees it as a personal challenge.

(I would have used a UDF, too. Those big formulas hurt my head!)
 
Back
Top