How to extract the numbers from text

[ttamil_201]

Hi team,

:banghead:I am struggling to extract 10 digit number from a text. That 10 Digit number can be anywhere in the text.

eg1 : HJ$1/1212322309/TPZ921/1S/KP N/W

Output of 1: 1212322309


eg2 : HJ$1/TPZ921/1212322309/1S/KP N/W

Output of 2: 1212322309

I have used Mid Function Expr1: Val(Mid([CM_PROBLEM_DESC],InStr(5,[CM_PROBLEM_DESC],"/")))

Pls help

 

[Ranman256]

This is bad because it can appear ANYWHERE, so there is no 1 place you can look using MID.

 

[Ranman256]

This may work. In a query , submit the field with the text and it should return the number...
usage: ParseNum([field])

Function ParseNum(ByVal pvVal)
Dim vWord, vNum, vSrc, vChr
Dim i As Integer

vSrc = pvVal
For i = 1 To Len(vSrc)
    vChr = Mid(vSrc, i, 1)
    If vChr = "/" Then
         If ValidateNum(vWord) Then ParseNum = vWord
         vWord = ""
    Else
       If IsNumeric(vChr) Then
          vWord = vWord & vChr
       Else
          vWord = ""
       End If
    End If
Next
End Function

Private Function ValidateNum(ByVal pvWord) As Boolean
If Len(pvWord) = 10 And IsNumeric(pvWord) Then ValidateNum = True
End Function

 

[JHB]

You can use the Split function.

 

[sxschech]

Another approach with regular expressions obtained from web. Tried it using your data and moving the 10 digits around and seems to handle the extract whether it is in beginning, middle or near end. May need to set a reference to Microsoft VBScript Regular Expressions.

Data                                  Result
1212322309/HJ$1/TPZ921/1S/KP N/W    1212322309
HJ$1/1212322309/TPZ921/1S/KP N/W    1212322309
HJ$1/TPZ921/1212322309/1S/KP N/W    1212322309
HJ$1/TPZ921/1S/KP N/1212322309/W    1212322309

Function ExtractEventCode(eventcode As String)
'http://stackoverflow.com/questions/31612586/regex-extracting-n-digit-numbers-from-string
'http://stackoverflow.com/questions/24167146/how-can-i-extract-a-unique-5-digit-number-from-a-string-using-a-vba-regex-in-exc
'20160615
    Dim objRegex As Object
    Dim regexMatches As Object
    Dim strIn As String
    Dim FirstPartID As String
    Dim SecondPartID As String


    'strIn = "Punktschweissen\3-Blech\ 1384156a RE und 1375188 ZB RE 20 PART 1"
    strIn = eventcode

    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Global = True
        .Pattern = "(\d{10})+"
        Set regexMatches = .Execute(strIn)
    End With
    If regexMatches.Count > 0 Then
        ExtractEventCode = regexMatches(0)
    End If
    'FirstPartID = regexMatches(0)  'sPartIDNumberArray(1)
    'SecondPartID = regexMatches(1) 'sPartIDNumberArray(2)
End Function

 

[ttamil_201]

Thanks for all, who worked for me, but i tried with the sxschech post it worked Thank you so much guys