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.

Would something like this work for you?

Function Check(s As String) As Boolean
Check = s Like "###.###.####.######.###.##.###"
End Function

Sub TestIt()
Debug.Print Check("333.333.3333.333333.333.33.333")
Debug.Print Check("A33.333.3333.333333.333.33.333")
Debug.Print Check("333.333.3333.333333.333.33")
End Sub

returns:
True
False (has letter A in it)
False

HTH
--
Dana DeLouis
Win XP & Office 2003


"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.

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.

Dana,

Thanks for your post. I'm afraid maybe mine wasn't clear. This may work in
VBA, but I need a SPREADSHEET function to do this. Something that can be
placed in the actual worksheet.

Again, thanks for posting, do you have any further ideas?

"Dana DeLouis" wrote:

Would something like this work for you?

Function Check(s As String) As Boolean
Check = s Like "###.###.####.######.###.##.###"
End Function

Sub TestIt()
Debug.Print Check("333.333.3333.333333.333.33.333")
Debug.Print Check("A33.333.3333.333333.333.33.333")
Debug.Print Check("333.333.3333.333333.333.33")
End Sub

returns:
True
False (has letter A in it)
False

HTH
--
Dana DeLouis
Win XP & Office 2003


"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.

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.

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.

quartz,

You may be surprised, but the code posted by Dana meets your criterion of
being sutable for placing in the actual worksheet. Just copy this code (the
function only) into a new module in VBA Editor, then go to your sheet and
write the following formula: =check(A1) and voila! you have either TRUE or
FALSE.

I would only suggest that Application.Volatile be added to the code, like
this:

Function Check(s As String) As Boolean

Application.Volatile
Check = s Like "###.###.####.######.###.##.###"
End Function

Anyway, below I have posted a true worksheet function solution for you:

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

will return TRUE or FALSE.

Regards,
KL



"quartz" wrote in message
...
Dana,

Thanks for your post. I'm afraid maybe mine wasn't clear. This may work in
VBA, but I need a SPREADSHEET function to do this. Something that can be
placed in the actual worksheet.

Again, thanks for posting, do you have any further ideas?

"Dana DeLouis" wrote:

Would something like this work for you?

Function Check(s As String) As Boolean
Check = s Like "###.###.####.######.###.##.###"
End Function

Sub TestIt()
Debug.Print Check("333.333.3333.333333.333.33.333")
Debug.Print Check("A33.333.3333.333333.333.33.333")
Debug.Print Check("333.333.3333.333333.333.33")
End Sub

returns:
True
False (has letter A in it)
False

HTH
--
Dana DeLouis
Win XP & Office 2003


"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.

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.

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.

Your formula returned True for this:

aaa.aaa.aaaa.aaaaaa.aaa.aa.aaa
as well as
...............................






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

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.

I tried it with
AAA.BBB.CCCC.DDDDDD.EEE.FF.GGG

and it returned true. Sure you entered the right number of characters in
your test?

by the way, I thought it was a clever solution.

--
Regards,
Tom Ogilvy

"KL" wrote in message
...
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.

I think you may have missed this portion of the OP's post:

....I just need to be sure all the "#" signs are numbers...

(in ###.###.####.######.###.##.###)

FWIW, I thought it was almost a neat solution <bg.

Tom Ogilvy wrote:

I tried it with
AAA.BBB.CCCC.DDDDDD.EEE.FF.GGG

and it returned true. Sure you entered the right number of characters in
your test?

by the way, I thought it was a clever solution.

--
Regards,
Tom Ogilvy

"KL" wrote in message
...
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

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.

a slightly cleaner version:

=(COUNTIF(A1;"???.???.????.??????.???.??.???")*(IS NUMBER(VALUE(SUBSTITUTE(A1;".";""))))*(LEN(SUBSTIT UTE(SUBSTITUTE(A1;".";"");",";""))=24))=1


"KL" wrote in message
...
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.

No, I saw that when I answered this morning and felt this solution had that
as a weakness. But it was a major step forward.

I figured combining it with

=ISNUMBER(SUBSTITUTE(A5,".","")*1)

would handle that, but never got around to testing it.
--
Regards,
Tom Ogilvy



"Dave Peterson" wrote in message
...
I think you may have missed this portion of the OP's post:

...I just need to be sure all the "#" signs are numbers...

(in ###.###.####.######.###.##.###)

FWIW, I thought it was almost a neat solution <bg.

Tom Ogilvy wrote:

I tried it with
AAA.BBB.CCCC.DDDDDD.EEE.FF.GGG

and it returned true. Sure you entered the right number of characters
in
your test?

by the way, I thought it was a clever solution.

--
Regards,
Tom Ogilvy

"KL" wrote in message
...
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

Hi,

I would only suggest that Application.Volatile be added to the code, like
this:

Very bad habit: Adding Volatile to function only make your model slower (always
recalculating even if the dependent cell have NOT changed) .
Make sure you add the dependent cell in your function's arguments and the
recalcs will occur appropriately.

Regards,

Daniel M.

Tom,

I think you were on the right track, but there is another contingency to be
taken into account:

333.333.3333..33333.333.33.317 (an extra "." replacing a number)
333.333.3333.,33333.333.33.318 (an extra "," replacing a number)

so there seems to be a need for 3 conditions:

=(COUNTIF(A1;"???.???.????.??????.???.??.???")*(IS NUMBER(VALUE(SUBSTITUTE(A1;".";""))))*(LEN(SUBSTIT UTE(SUBSTITUTE(A1;".";"");",";""))=24))=1

Not as neat as I hoped to have it, but works.

KL

"Tom Ogilvy" wrote in message
...
No, I saw that when I answered this morning and felt this solution had
that
as a weakness. But it was a major step forward.

I figured combining it with

=ISNUMBER(SUBSTITUTE(A5,".","")*1)

would handle that, but never got around to testing it.
--
Regards,
Tom Ogilvy



"Dave Peterson" wrote in message
...
I think you may have missed this portion of the OP's post:

...I just need to be sure all the "#" signs are numbers...

(in ###.###.####.######.###.##.###)

FWIW, I thought it was almost a neat solution <bg.

Tom Ogilvy wrote:

I tried it with
AAA.BBB.CCCC.DDDDDD.EEE.FF.GGG

and it returned true. Sure you entered the right number of characters
in
your test?

by the way, I thought it was a clever solution.

--
Regards,
Tom Ogilvy

"KL" wrote in message
...
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

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,

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,

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

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...adsl.xs4all.nl

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(A 7,"???.???.????.??????.???.??.???"))*(LEN(SUBSTITU TE(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" wrote in message
...
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

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,

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

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

Have you figured out the OP's resistance to the UDF? It's so simple....


On Wed, 17 Nov 2004 19:48:58 -0600, Dave Peterson
wrote:

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

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!)



Myrna Larson wrote:

Have you figured out the OP's resistance to the UDF? It's so simple....

On Wed, 17 Nov 2004 19:48:58 -0600, Dave Peterson
wrote:

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