Need help on a code extracting data

[meatcan]

Hi all,

this is my first post here and i hope you guys can give me a little help here ))

I need to search for and extract an alphanumerical string that is either in the form of AXXXX, A XXXX, BXXXX or B XXXX, with XXXX being any 4 digit numbers.

The problem is that the string that I need are in a column that look something like this,

123ABCAXXXX617
6352HGY(BXXXX)
j23bb2h6

In other words, the cell may contain the string, with possibly any numbers, letters, symbols or space before and after them.

In some cells the string may not even be found in it, and I would have to ignore that entry.

I know it may be insanely hard with the unknown XXXX and any form of possible things before and after the string.

I thought of using MID and INSTR(or Find) to find the position of A and B before extracting the 4 numbers after it, but I think INSTR would only return the first A it finds....

Any help rendered would be deeply appreciated!!!

 

[MickG]

Try this for data in column "A", Results in column "B"

[COLOR="Navy"]Sub[/COLOR] MG19Jun33
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Temp    [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Temp = Dn
Temp = Replace(Temp, " ", "")
[COLOR="Navy"]For[/COLOR] n = 1 To Len(Temp)
    [COLOR="Navy"]If[/COLOR] Mid(Temp, n, 1) = "A" Or Mid(Temp, n, 1) = "B" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] IsNumeric(Mid(Temp, n + 1, 4)) [COLOR="Navy"]Then[/COLOR]
            c = c + 1
             Cells(c, "B") = Mid(Temp, n, 5)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[pgc01]

Hi
Welcome to the board

Try this udf. With the string in A1, in B1:

=GetString(A1)

Function GetString(s As String) As String
Static rex As Object
Dim rexMatches As Object

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

Set rexMatches = rex.Execute(s)
If rexMatches.Count = 1 Then GetString = rexMatches(0)
End Function

 

[pgc01]

Hi Mick

        [COLOR=navy]If[/COLOR] IsNumeric(Mid(Temp, n + 1, 4)) [COLOR=navy]Then[/COLOR]

This does not tell you if you have 4 digits. It tells you that you have a 4 character string that can be converted to a number.
You must test each one of the 4 digits.

Ex., execute and see the result in the Immediate Window:

Sub Test_IsNumeric()

Debug.Print IsNumeric("1.234")
Debug.Print IsNumeric("   4")
Debug.Print IsNumeric("1   ")
Debug.Print IsNumeric("123.")
Debug.Print IsNumeric("1e23")
Debug.Print IsNumeric("0,1.")
Debug.Print IsNumeric("0,,,")
End Sub

 

[meatcan]

thanks so much everybody

another thing..how do i get it to return the row number also?
i needed to extract something else from the row if the string is in the cell

 

[Rick Rothstein]

In addition to PGC's RegExp UDF (user defined function), here is a UDF that use native VBA functionality only...

Function GetString(Source As String) As String
  Dim X As Long
  For X = 1 To Len(Source)
    If Mid(Source, X, 5) Like "*[AaBb]####*" Then
      GetString = Mid(Source, X, 5)
    ElseIf Mid(Source, X, 6) Like "*[AaBb] ####*" Then
      GetString = Mid(Source, X, 6)
    End If
  Next
End Function

 

[Rick Rothstein]

another thing..how do i get it to return the row number also?
i needed to extract something else from the row if the string is in the cell

You will have to clarify this question for us. The only non-UDF solution offered to you may not work correctly (see Message #4)... with the UDF solutions, you know the Row number as the formula is in that row. Perhaps if you tell us how you plan to use whichever method posted so far (Message #3 and #6) along with what you plan to use the row number for the extraction you just mentioned, perhaps we can give you a more focussed answer.

 

[meatcan]

Rick I used your code and I think I carry on with it by myself.
You guys made this seem so easy. I still have so much to learn.

Thank you everyone

 

[MickG]

Hi, PGC,
Thanks for the comments & code.
Something to be aware of for future reference
Regards Mick

 

[pgc01]

Hi Mick, you're welcome.

It was an error easy to spot, as I remember doing it myself!