Extract string from text using VBA

Question

I have an excel sheet with URLs that are a dumped from the server, I have the task of matching certain patterns and extracting the IDs, I am not an expert in RegExp but have got a start.

Can you please look at the example below and let me know how best to take the IDs out, Thanks in advance

("https://abcd.com/000001","Goods1")

Here I need to take out 000001

Function ExtractIds(urlstr As String)
    Dim reg
    Dim rng As Range, i As Long, j As Long
    Dim mtch, mt As String
    Set reg = CreateObject("vbscript.regexp")
    With reg
        .IgnoreCase = True
        .MultiLine = False
        .Pattern = "/(.*?)"""
        .Global = False
    End With
    MsgBox reg.Test(tmpStr)
    If reg.Test(tmpStr) Then
        ExtractIds = reg.Execute(tmpStr)(0).SubMatches(0)
    End If
End Function

Is the pattern always like what you have mentioned? https://abcd.com/ followed by the ID? — Pankaj Jaju
– Pankaj Jaju, Commented May 21, 2015 at 11:47

Gary's Student · Accepted Answer · 2015-05-21 11:40:08Z

2

How about:

Public Function ExtractIds(urlstr As String)
   ary = Split(urlstr, "*")
   ExtractIds = ary(2)
End Function

enter image description here

EDIT#1:

Based on your Edit use this instead:

Public Function ExtractIds(urlstr As String)
   Dim DQ As String
   DQ = Chr(34)
   ary = Split(urlstr, DQ)
   bry = Split(ary(1), "/")
   ExtractIds = bry(UBound(bry))
End Function

edited May 21, 2015 at 11:40

answered May 21, 2015 at 11:05

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Avijit Over a year ago

There was a typo in my post. ID is not surrounded by *. I have updated.

rohrl77 Over a year ago

@Gary'sStudent Awesome answer! Love the creative aspect of it and the simplicity.

Avijit Over a year ago

@Gary'sStudent I must say you are very much creative persion.

Collectives™ on Stack Overflow

Extract string from text using VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related