Extracting if there 8 numbers combinations in string

[Ks1102]

Dear Experts

Would you help i have code using for extract number if 8 Digi combination .. but something wrong for counts if more the eight ... to extracted .

For example :
String : HAL LOCKER # P2018061101136 / T# 96825809 before act .
the code would extracted 20180611 .... Not the 96825809 in string

Thanks very much your help

Public Function GetMy8Digits(cell As Range)
Dim s As String
Dim i As Integer
Dim answer
Dim counter As Integer
s = cell.Value
counter = 0
For i = 1 To Len(s)
    If IsNumeric(Mid(s, i, 1)) = True Then

        answer = answer + Mid(s, i, 1)
        counter = counter + 1

        If counter = 8 Then
            GetMy8Digits = answer
            Exit Function
        End If
     Else
     counter = 0
     answer = ""
    End If
Next i
End Function

 

[Trixterz]

Try this and let me know if it works...

Public Function GetMy8Digits(Cell As Range)
    Dim CheckString As Variant
   
    CheckString = Trim(Split(Cell.Value, "T#")(1))
    CheckString = Split(CheckString, " ")(0)
   
    If IsNumeric(CheckString) And Len(CheckString) = 8 Then
        GetMy8Digits = CheckString
    Else
        GetMy8Digits = "0"
    End If
End Function

 

[FormR]

Hi, here is an alternative you can try.

Public Function GetMy8Digits(cell As Range)
Dim v As Variant
For Each v In Split(cell.Value, " ")
    If Len(v) = 8 And IsNumeric(v) Then
        GetMy8Digits = v
        Exit Function
    End If
Next v
End Function

 

[Ks1102]

Hi, here is an alternative you can try.

Public Function GetMy8Digits(cell As Range)
Dim v As Variant
For Each v In Split(cell.Value, " ")
    If Len(v) = 8 And IsNumeric(v) Then
        GetMy8Digits = v
        Exit Function
    End If
Next v
End Function

Thank you, reply you after trying.

 

[Ks1102]

Try this and let me know if it works...

Public Function GetMy8Digits(Cell As Range)
    Dim CheckString As Variant
  
    CheckString = Trim(Split(Cell.Value, "T#")(1))
    CheckString = Split(CheckString, " ")(0)
  
    If IsNumeric(CheckString) And Len(CheckString) = 8 Then
        GetMy8Digits = CheckString
    Else
        GetMy8Digits = "0"
    End If
End Function

Dear formR

this code getting error "CheckString = Trim(Split(Cell.Value, "T#")(1))"

But another one work perfectly .. thanks so much

 

[Trixterz]

Oops, didn't see the last part of your comment. See the code below to retrieve the first set of numbers instead of the last set of numbers with the above code.

Public Function GetMy8Digits(cell As Range)
    Dim CheckString As Variant
    CheckString = cell.value
    CheckString = Trim(Split(CheckString, "#")(1))
    CheckString = VAL(Split(CheckString, " ")(0))
  
    If IsNumeric(CheckString) And Len(CheckString) =< 8 Then
        GetMy8Digits = CheckString
    Else
        GetMy8Digits = "0"
    End If
End Function

 

[mohadin]

And one more option

Function Get8Digits(r As Range)
    With CreateObject("VBScript.RegExp")
        .Pattern = "(# )(\d+)"
        If .test(r.Value) Then
            Get8Digits = .Execute(r.Value)(0).submatches(1)
        End If
    End With
End Function

 

[Ks1102]

And one more option

Function Get8Digits(r As Range)
    With CreateObject("VBScript.RegExp")
        .Pattern = "(# )(\d+)"
        If .test(r.Value) Then
            Get8Digits = .Execute(r.Value)(0).submatches(1)
        End If
    End With
End Function

THANKS , IT'S WORK GREAT ..

 

[mohadin]

Great
So glad I could help
Thank you for the feedback
Be Happy

 

[Peter_SSs]

THANKS , IT'S WORK GREAT ..

So the problem actually had nothing to do with 8 digits but just extract whatever number of digits followed "# "?

For example:

Ks1102.xlsm
	A	B
1	HAL LOCKER # 201 / T# 96825809 before act	201
2	HAL LOCKER # P2018061101136 / T# 96825809569812356111 before act	96825809569812356111
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=Get8Digits(A1)