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.

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.

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.

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.

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.

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.

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.