Find data in string of text

[joelkoch]

I have a column of cells that have a bunch of text in them. in that text there are serial numbers I would like to extract. A cell could have multiple different serial numbers that need to be extracted. For example a cell could have: "Exchange + PS43138 Exchange + PS43178" I'm interested in trying to get both the serial numbers out PS43138 and PS43178. the serial numbers differ slightly, but they all have a 5 digit string of numbers. I tried to create a UDF but can'f figure out how to get all serial numbers out, I can only get the first one. This is the UDF I got from the looking through the forum.

Function FiveDigitNo(s As String) As StringWith CreateObject("VBScript.RegExp")
  .Pattern = "(?:^|\D)(\d{5})(?!\d)"
  If .Test(s) Then FiveDigitNo = .Execute(s)(0).SubMatches(0)
End With
End Function

Any help would be much appreciated.

Thanks,

 

[kweaver]

Are they all PS prefix? Are the serial numbers always after a + sign? Does "Exchange" appear in each row?

 

[joelkoch]

Unfortunately the only consistent between them is that there is a 5 digit number sequence at the end.

 

[kweaver]

Do all the 5-digit numbers begin with some 2-character prefix?

 

[joelkoch]

They do not.

 

[kweaver]

So, does that mean you want the 5-digit number prefixed with what is before it but after the 1st space (actually, the 2nd space from the beginning of the string)?

 

[joelkoch]

I there would be a way to pull out all the 5 digit numbers and put those in a cell separated by a space that would work. Like in the example the formula could extract the two 5 digit numbers in a new cell like this "43138 43178". Ideally putting them in separate cells would be best, but that is just extra steps. Honestly they don't even need to be separated by a space because I believe I could just text to columns them apart anyway. As far as I can see those 5 digit numbers appear to be unique so they are all I really need to do my analysis.

 

[MoshiM]

Try something like this

Dim Counter As Long, STR_5 As String, Original_String As String


Original_String = 'set equal to range or cell where the original string is found or equal to another variable
 
For Counter = 1 To Len(Original_String)




    If IsNumeric(Mid(Original_String, Counter, 1)) Then
    
        STR_5 = Mid(Original_String, Counter-2, 7)
        
        'now ouput STR_5 where you want it to be
        
        Counter = Counter + 4
        
    End If


Next Counter

 

[joelkoch]

Thank you for the reply.

I'm having trouble getting the VBA to work. If the name of the Sheet is called "Data" and the serial number are in column H and the output should go in column T how would I write this? Thank you for your help on this.

 

[kweaver]

With your text strings in A1:A?, try this which should put the numbers in the B column.

Sub ExtractNumbers()

    Dim i As Long, j As Long
    Dim xCell As Range
    Dim xRg As Range
    Dim xTxt As String
    Dim xStr As String
    Dim xUpdate As Boolean
    Dim LR As Long
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    For j = 1 To LR
    Range("B" & j).FormulaArray = "=TEXTJOIN(,1,TEXT(MID(A" & j & ",ROW($A$1:INDEX($A$1:$A$1000,LEN(A" & j & "))),1),""#;-#;0;""))"
    Next j
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Range("$B$1:$B$" & LR)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    xUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    For Each xCell In xRg
        
        xStr = xCell.Text
        xTxt = ""
        For i = 1 To Len(xStr) Step 5
            If xTxt = "" Then
                xTxt = Mid(xStr, i, 5)
            Else
                xTxt = Trim(xTxt) & " " & Mid(xStr, i, 5)
            End If
        Next
        xCell = xTxt
    Next
    Application.ScreenUpdating = xUpdate
End Sub

BTW: This assumes you are running the latest version of Excel (e.g., 365) with TEXTJOIN as a function.