finding text that matches format criteria in string,

[cbrf23]

I'm trying to restructure a list of files at work into a format that makes some sort of sense and can be filtered into useful information.

Part of what I need to do, is match up all the drawing numbers that a certain project references. The numbers are formatted like "####A##" ex. 1234A01,1234A02,5678A01, etc. The cells that I need to extract drawing numbers from contain some description or other text (not just the drawing number) so I need to extract the drawing number from that cells value. (Ex. I need to get the drawing number 1234A01 from a cell whose value is "blah blah 1234A01 blah blah blah")

I was thinking I could just search the string for "####A##" but I'm not really sure how....I tried instr, but I think its searching for the literal value of "####A##" rather than treating the # characters as wildcards...

Can anyone offer some suggestions on this?

I tried:

if rngNames.Cells(intx, 2).Value Like ("*####A##*") then
set intStart = instr(1,rngnames.Cells(intx,2).value,"####A##")
'....
end if

but intStart remains 0, so this method is not working...

 

[MickG]

If you Text/Numbers are in column "A" then this should place the numbersa in column "B".

Dim Rng     As Range
Dim Dn      As Range
Dim num     As Variant
Dim n       As Variant
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
For Each Dn In Rng
    num = Split(Dn, " ")
    For Each n In num
        If Len(n) = 7 And IsNumeric(Left(n, 4)) And IsNumeric(Right(n, 2)) And Mid(n, 5, 1) = "A" Then
            Dn.Offset(, 1) = n
            Exit For
        End If
    Next n
Next Dn

Mick

 

[cbrf23]

Nice. Thats Pretty clever, I like that...

I found some code online for extracting the nth word from a string and modified that to extract a word in the specified format. Here is that code as well.

Function ExtractLikeThis(TheString As String, Separator As String, TheFormat As String) As String
    Dim strString As String
    Dim strTemp As String
    Dim intX As Integer
    
    strString = TheString
    If Separator = Chr(32) Then strString = Application.Trim(strString)
    If Right(strString, 1) <> Separator Then strString = strString & Separator
    strTemp = vbNullString
    
        For intX = 1 To Len(strString)
                If Mid(strString, intX, 1) = Separator Then
                        If strTemp Like TheFormat Then
                            ExtractLikeThis = strTemp
                            Exit Function
                        Else
                            strTemp = vbNullString
                        End If
                Else
                    strTemp = strTemp & Mid(strString, intX, 1)
                End If
        Next intX
    ExtractLikeThis = vbNullString
End Function

 

[cbrf23]

This came in handy again today!