Finding specific strings

S

steve astrop

New Member

Joined

Nov 18, 2002

Messages

23

• Jan 5, 2010

• #21

Hi Sandeep this UDF

Function FindSpec(InputStr As String, Text1 As Long, Num As Long, Text2 As Long) As String
Dim Pat As String
Pat = "\b[A-Za-z]{" & Text1 & "}\d{" & Num & "}[A-Za-z]{" & Text2 & "}\b"
With CreateObject("vbscript.regexp")
.Pattern = Pat
.Global = True
If .Test(InputStr) Then
FindSpec = "Hit"
Else
FindSpec = "No Match"
End If
End With
End Function

It works very well. How would I change the pat to include another number at the beginining and the end?

Kend regards

Steve

 

Upvote 0

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• Jan 5, 2010

• #22

steve astrop said:

When I manually change the data it does change to TRUE.

Click to expand...

Check in the options if you have Calculation set to manual.

In excel 2007: Excel Options->Formulas->Calculation options

In prior versions: Tools->Options->Calculation <!-- / message --><!-- sig -->

 

Upvote 0

Sandeep Warrier

Well-known Member

Joined

Oct 31, 2008

Messages

2,680

• Jan 6, 2010

• #23

steve astrop said:

...
It works very well. How would I change the pat to include another number at the beginining and the end?

Kend regards

Steve

Click to expand...

Hi

You can change

Pat = "\b[A-Za-z]{" & Text1 & "}\d{" & Num & "}[A-Za-z]{" & Text2 & "}\b"

to

Pat = "\b\d{" & StartNum & "}[A-Za-z]{" & Text1 & "}\d{" & Num & "}[A-Za-z]{" & Text2 & "}\d{" & EndNum & "}\b"

and add the 2 variables StartNum and EndNum to the Function.

 

Upvote 0

S

steve astrop

New Member

Joined

Nov 18, 2002

Messages

23

• Jan 6, 2010

• #24

Thanks to everyone for all their assistance with this. Works very well now.

Best wishes

Steve

 

Upvote 0

S

steve astrop

New Member

Joined

Nov 18, 2002

Messages

23

• Jan 6, 2010

• #25

Just when I thought it was all going well!
This UDF doesnt work if the number is 18 chars (or more) long?

Pat = "\b\d{" & StartNum & "}[A-Za-z]{" & Text1 & "}\d{" & Num & "}[A-Za-z]{" & Text2 & "}\d{" & EndNum & "}\b"

Any suggestions?

Thanks in advance.

Steve

 

Upvote 0

Sandeep Warrier

Well-known Member

Joined

Oct 31, 2008

Messages

2,680

• Jan 7, 2010

• #26

steve astrop said:

Just when I thought it was all going well!
This UDF doesnt work if the number is 18 chars (or more) long?

Pat = "\b\d{" & StartNum & "}[A-Za-z]{" & Text1 & "}\d{" & Num & "}[A-Za-z]{" & Text2 & "}\d{" & EndNum & "}\b"

Any suggestions?

Thanks in advance.

Steve

Click to expand...

No. of characters shouldn't make any difference. Did you put the StartNum and EndNum in the Function header?

The UDF seems to work fine for the case below.

Excel Workbook
	A	B
1	123456789123456789123456789ABCDE123456789123456789ABCDEFGH123456789123456789123456789123456789	Hit
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B1	=FindSpec(A1,27,5,18,8,36)

Code:

Function FindSpec(InputStr As String, StartNum As Long, Text1 As Long, Num As Long, Text2 As Long, EndNum As Long) As String
    Dim Pat As String
    Pat = "\b\d{" & StartNum & "}[A-Za-z]{" & Text1 & "}\d{" & Num & "}[A-Za-z]{" & Text2 & "}\d{" & EndNum & "}\b"
    With CreateObject("vbscript.regexp")
        .Pattern = Pat
        .Global = True
        If .Test(InputStr) Then
            FindSpec = "Hit"
        Else
            FindSpec = "No Match"
        End If
    End With
End Function

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

Upvote 0

S

steve astrop

New Member

Joined

Nov 18, 2002

Messages

23

• Jan 7, 2010

• #27

Yes Startnum and Endnum are in the header.. It doesnt appear to be working on on fileds that are fomatted as a number and are more than 16 digits.. I cant change the format as they are reference numbers. When I change it to text it changes from 110312041317725000
to 1.10312E+17


So Im now stuck again!

Regards

Steve

 

Upvote 0

Sandeep Warrier

Well-known Member

Joined

Oct 31, 2008

Messages

2,680

• Jan 7, 2010

• #28

The InputStr is defined to be a String and hence it might not work properly if proper numbers are input.

Try changing the definition of InputStr from String to Variant.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

Upvote 0