Nice perseverance!

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





KL wrote:

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

"Dave Peterson" wrote in message
...
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.)

KL wrote:

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,"???.???.????.??????.???.??.???")*(IS NUMBER(VALUE(SUBSTITUTE(A1,".",""))))*(LEN(TEXT(SU BSTITUTE(A1,".",""),"#######################0"))=2 4))=1

Cheers,
KL

"Dave Peterson" wrote in message
...
Excel can be a pain:
123.123.1234.123123.123.12.e23
returns True. (Scientific notation problems)


I think that this array formula works, though:

=((COUNTIF(A2,"???.???.????.??????.???.??.???")=1)
*(LEN(SUBSTITUTE(A2,".",""))=24))
*(MIN(CODE(MID(SUBSTITUTE(A2,".",""),
ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2,".","")))),1)) )=48)
*(MAX(CODE(MID(SUBSTITUTE(A2,".",""),
ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2,".","")))),1)) )<=57)
=1

(all one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If
you
do it
correctly, excel will wrap curly brackets {} around your formula.
(don't
type
them yourself.)



KL wrote:

Alright, sorry - didn't understand your comment. I focused on
evaluating
the
pattern, but forgot about the requirement to have numbers only. So
your
question sounded to me like if I replace a number with a letter the
formula
will fail to recognise the pattern, which is not true.

Anyway, I still insist you can achieve this without VBA and here is my
suggested formula (a bit long, but it seems to work):

=((COUNTIF(A1;"???.???.????.??????.???.??.???")=1) *(ISNUMBER(VALUE(SUBSTITUTE(A1;".";""))))*(LEN(SUB STITUTE(SUBSTITUTE(A1;".";"");",";""))=24))=1

Regards,
KL

"Myrna Larson" wrote in message
...
I didn't have to try it. I've read the documentation <g.

The question mark matches *any* character.

Maybe you didn't missed something in the original question. The
quote
that
I
cited came from a message from the OP. He requires that the
wild-card
characters be digits, not letters or symbols.

To solve his problem efficiently requires a VBA macro, which has
already
been
posted.



KL wrote:

Myrna,

Have you tried it? It works like charm to me in either case (Excel
2000
SP-3).

KL

"Myrna Larson" wrote in
message
...
Isn't that solution also subject to this problem: "although if
any
of
these
items contains a letter rather than a number it will fail" ?

On Mon, 15 Nov 2004 15:30:33 +0100, "KL"
wrote:

try this:

=COUNTIF(A1;"???.???.????.??????.???.??.???")= 1

Regards,
KL

"quartz" wrote in message
...
Thanks Tom.

Yes, the periods are hard written into the cells. I guess I
could
use
MID
to
ensure that they are there and match the positions of the
periods
rather
than
looking at the overall pattern.

This may be close enough, although if any of these items
contains
a
letter
rather than a number it will fail. If you have any other ideas
please
let
me
know.

Thanks again.

"Tom Ogilvy" wrote:

are the periods in the cell or are they produced through
formatting.

If in the cell, you could use an AND statement

=AND(mid(A1,4,1)=".",Mid(A1,8,1)=".", . . .

It depends on what the possibilities are.

But the simple answer is that there is no built in function
that
is
designed
to do this.

--
Regards,
Tom Ogilvy



"quartz" wrote in message
...
I need a spreadsheet function or formula that can match by
a
pattern.
Is
there such a thing? I have looked at FIND and SEARCH, but
for
these
you
need
to specify exactly your search string, you can't just
indicate
a
pattern
to
match.

For example, I need to test to determine if a cell contains
a
series
of
numbers in the following pattern:

###.###.####.######.###.##.###

If so, then evaluate to ONE or TRUE or whatever, if not
then
ZERO
or
FALSE
or whatever. NOTE: The above string could contain any
series
of
digits,
I
just need to be sure all the "#" signs are numbers and that
they
are
in
the
pattern shown above.

Your assistance is greatly appreciated. Thanks in advance.









--

Dave Peterson

--

Dave Peterson

--

Dave Peterson