Finding specific strings

[steve astrop]

Hi, Im not really sure of the formula I need so I havent suggested. I have tried len, Istext etc but got stuck.

I have a combination of letters and numbers in a column. For example.

xx1234567x
1234567xx
xx1234xx1234

I need a formula to tell whether they begin with two letters and have a certain number of numbers then end in letters. Or have letters in the middle. Sorry Im so vague, I just need a start in the right direction. Ideally combined with an IF statement.

In english (If two letters afetr nine numbers then yes)

Kindest regards

Steve

 

[steve astrop]

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

 

[pgc01]

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

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

 

[Sandeep Warrier]

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

Kend regards

Steve

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.

 

[steve astrop]

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

Best wishes

Steve

 

[steve astrop]

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

 

[Sandeep Warrier]

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

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)

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

 

[steve astrop]

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

 

[Sandeep Warrier]

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