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