Finding Specific Letter and Number Strings

[jpedges38]

Say I have these two lines in Column A:

3051S2TG4A2A11X5AWA3WK1I5A1140
3051S2LD2AA1A1030DFF71CA00


I need to be able to have a formula in Column B that Finds the letter "A" when it is followed by four numbers, and then return "A and the 4 numbers."

So for the first line, the result in Column B would be "A1140," and the second line result would be "A1030"

Any help would be much appreciated.

Thanks,
J

 

[pgc01]

Hi

In B1:

=GetCode(A1)

Try this udf:

Function GetCode(s As String) As String
Static rex As Object

If rex Is Nothing Then
    Set rex = CreateObject("VBScript.RegExp")
    rex.Pattern = "A\d{4}"
End If

If rex.test(s) Then GetCode = rex.Execute(s)(0)
End Function

 

[Biz]

Hi

In B1:

=GetCode(A1)

Try this udf:

Function GetCode(s As String) As String
Static rex As Object

If rex Is Nothing Then
    Set rex = CreateObject("VBScript.RegExp")
    rex.Pattern = "A\d{4}"
End If

If rex.test(s) Then GetCode = rex.Execute(s)(0)
End Function

Very nicely done.

Biz

 

[mps12345]

This is close to what I am looking for. I need to recognize a pattern of two letters (upper or lower) followed by five numbers in a string of almost random characters.
Example:
Cell A1 = "|: BB12345 ID:" Function should return "BB12345"
Cell A2 = "Fd43546 Login:|" Function should return "Fd43546"
Cell A3 = " SOE : fr83745" Function should return "fr83745"

Any help is greatly appreciated.

BTW, it is 2007 excel

 

[pgc01]

Hi
Welcome to the board

That's a similar question.

Try replacing the pattern:

    rex.Pattern = "[A-Za-z]{2}\d{5}"

 

[mps12345]

Thanks for the reply. I am not good with VBA. I pasted the the code to the VBA for Sheet1 (Sheet1) and ThisWorkbook, but all I get is "#NAME?" when I use the function =GetCode(A1). I have no idea what I am doing wrong.

Exactly what I pasted;

Function GetCode(s As String) As String
Static rex As Object
If rex Is Nothing Then
Set rex = CreateObject("VBScript.RegExp")
rex.Pattern = "[A-Za-z]{2}\d{5}"
End If
If rex.test(s) Then GetCode = rex.Execute(s)(0)
End Function

 

[mps12345]

I figured it out. Thanks again!

 

[pgc01]

You're welcome. Thanks for the feedback.

Remark:
The code defines a Static variable. Whenever you change the code don't forget to press the reset button.

 

[mps12345]

Thanks for the tip, but what do you mean by the reset button? I cannot find anything about reset button except "custom radio buttons".


On another topic, I now need to find a string of 10 digits in a random string of characters. Example: "Jkdp049|| SOE84 ID: | 1234567890LD" should yield "1234567890".
I tried to modify the code you gave me, but it does not work. Below is what I tried.

Function GEID(s As String) As String
Static rex As Object
If rex Is Nothing Then
Set rex = CreateObject("VBScript.RegExp")
rex.Pattern = "d{10}"
End If
If rex.test(s) Then GEID = rex.Execute(s)(0)
End Function


Sorry to come back again, but I could not find anything similar to this on the site.

 

[pgc01]

Thanks for the tip, but what do you mean by the reset button? I cannot find anything about reset button except "custom radio buttons".

In the VBEditor in both the standard and the debug toolbars the Reset button is a blue square. It's usually close to the Run button that is a green triangle pointing to the right.
Because you have a static variable, whenever you change the code you should press the Reset button to reset the variable.

On another topic, I now need to find a string of 10 digits in a random string of characters. Example: "Jkdp049|| SOE84 ID: | 1234567890LD" should yield "1234567890".

rex.Pattern = "d{10}"

"d" - means the letter d
"\d" - means any digit

try:

rex.Pattern = "\d{10}"