Is there a formula that can pattern match?

[Guest]

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 DeLouis]

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

 

[Tom Ogilvy]

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.

 

[Guest]

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?

 

[Guest]

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.

 

[KL]

try this:

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

Regards,
KL

 

[KL]

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

 

[Myrna Larson]

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

 

[KL]

Myrna,

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

KL

 

[Dave Peterson]

Your formula returned True for this:

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

 

[Myrna Larson]

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.

 

[Tom Ogilvy]

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.

 

[Dave Peterson]

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

 

[KL]

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(SUBSTITUTE(SUBSTITUTE(A1;".";"");",";""))=24))=1

Regards,
KL

 

[KL]

a slightly cleaner version:

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

 

[Tom Ogilvy]

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

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

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

Myrna,

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

KL

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

 

[Daniel.M]

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.

 

[KL]

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;"???.???.????.??????.???.??.???")*(ISNUMBER(VALUE(SUBSTITUTE(A1;".";""))))*(LEN(SUBSTITUTE(SUBSTITUTE(A1;".";"");",";""))=24))=1

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

KL

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

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

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

Myrna,

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

KL

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

 

[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]

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