# Extracting if there 8 numbers combinations in string

#### Ks1102

##### Well-known Member
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

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

counter = counter + 1

If counter = 8 Then
Exit Function
End If
Else
counter = 0
End If
Next i
End Function``````

### 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
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
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
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

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
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``````

##### Well-known Member

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
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 ..

##### Well-known Member
Great
Thank you for the feedback
Be Happy

#### Peter_SSs

##### MrExcel MVP, Moderator
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)

Replies
0
Views
51
Replies
1
Views
71
Replies
5
Views
95
Replies
9
Views
132
Replies
0
Views
73