CODEWITHSUNDEEP
excelvba
kraljevocs
kraljevocs

Reputation: 115

How do I get the specific index of a string

I am writing a macro in Excel where I need to extract a Serial Number from a String which is separated by comma.

"Dell XPS 2015,6CK23AV,5BO039D3UE0,3y3y3y"
S/N: 5BO039D3UE0

"7CG9254UIE,MacBook Pro, L3C65AV,3y3y07"
S/N: 7CG9254UIE

My output would ultimately be the S/N numbers only. I tried InStr and Split. Can anyone help?

Upvotes: 0

Views: 116

Answers (1)

Kin Siang
Kin Siang

Reputation: 2699

You may extract the product ID by using regex function, in your example, the sub successful extract both product ID, here is the solution:

Sub regex()

Dim strPattern As String, findID As String, result As String
Dim RE As New RegExp
Dim match As Object

strPattern = "\d[A-Za-z0-9]{9,10}"
findID = "7CG9254UIE,MacBook Pro, L3C65AV,3y3y07"

With RE
    .Global = True
    .MultiLine = True
    .IgnoreCase = False
    .Pattern = strPattern
End With

Set match = RE.Execute(findID)

If match.Count <> 0 Then
    result = match.Item(0)
End If

Debug.Print result
End Sub

Upvotes: 1

Related Questions