Extracting if there 8 numbers combinations in string

Ks1102

Well-known Member
Joined
Jan 8, 2008
Messages
682
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
Rich (BB code):
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Trixterz

Board Regular
Joined
Aug 15, 2019
Messages
58
Try this and let me know if it works...

VBA Code:
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,434
Office Version
  1. 365
Platform
  1. Windows
Hi, here is an alternative you can try.

VBA Code:
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

Well-known Member
Joined
Jan 8, 2008
Messages
682
Hi, here is an alternative you can try.

VBA Code:
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

Well-known Member
Joined
Jan 8, 2008
Messages
682

ADVERTISEMENT

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

VBA Code:
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

Board Regular
Joined
Aug 15, 2019
Messages
58
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.

VBA 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

Well-known Member
Joined
Mar 22, 2015
Messages
603
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

And one more option
VBA Code:
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

Well-known Member
Joined
Jan 8, 2008
Messages
682
And one more option
VBA Code:
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

Well-known Member
Joined
Mar 22, 2015
Messages
603
Office Version
  1. 2013
Platform
  1. Windows
Great
So glad I could help
Thank you for the feedback
Be Happy
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,784
Office Version
  1. 365
Platform
  1. Windows
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
AB
1HAL LOCKER # 201 / T# 96825809 before act201
2HAL LOCKER # P2018061101136 / T# 96825809569812356111 before act96825809569812356111
Sheet1
Cell Formulas
RangeFormula
B1:B2B1=Get8Digits(A1)
 

Watch MrExcel Video

Forum statistics

Threads
1,112,784
Messages
5,542,500
Members
410,559
Latest member
jordansmith6532
Top