Extract numbers from alphanumeric strings

[Maurizio]

Hi all,

how can I extract (by VBA) this numbers:

149575
191368
191369

with prefix "DUD" and suffix "/nn" from alphanumeric strings with so DB:

DUD 149575/18
DUD 191368/17 Ton 39.00, DUD 191369/18 Ton 9.04


Tia. Maurizio

 

[Peter_SSs]

You didn't specify whether the number to be extracted is always 6 digits like your 3 examples.

Would this user-defined function be any use? This one allows for any number of digits between "DUD " and "/nn"
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across and down.

Function GetNums(s As String, num As Long) As Variant
  Static RX As Object, m As Object
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = "(DUD )(\d+)(?=\/\d\d)"
  GetNums = ""
  If num <= RX.Execute(s).Count Then GetNums = Val(RX.Execute(s)(num - 1).Submatches(1))
End Function

Excel Workbook
	A	B	C	D
1	DUD 149575/18	149575
2	DUD 191368/17 Ton 39.00, DUD 191369/18 Ton 9.04	191368	191369
Sheet1

If the number of digits is always 6, the above will still work but an alternative would be

Function Get_Nums(s As String, num As Long) As Variant
  Dim i As Long, j As Long
  
  Get_Nums = ""
  For i = 1 To Len(s) - 12
    If Mid(s, i, 13) Like "DUD ######/##" Then
      j = j + 1
      If j = num Then
        Get_Nums = Val(Mid(s, i + 4, 6))
        Exit For
      End If
    End If
  Next i
End Function

 

[Maurizio]

Peter thnks!!!

 

[Peter_SSs]

Peter thnks!!!

You're welcome.