Extract substring from a string in VBA

Question

I am looking to write some VBA code to obtain the following pieces of information from the following string with brackets and hyphens, i.e.

ACOST 2012 Pricing Table - (PRJ 216664 - Financial Server Decommission) - 12 Month Term

Using VBA for Excel 2007, I need to obtain the following two bits within this string and assigned to two different variables, i.e.:

216664
Financial Server Decommission

I tried the Mid() syntax but couldn't extract these two bits of info.

Is the format always going to be like that i.e (xxx xxxxxx - xxxxxxxxxxxxx) — Siddharth Rout, Jun 21 '12 at 14:59
Numerous related questions already exist on SO. Ex: [here](http://stackoverflow.com/q/1624387/190829), [here](http://stackoverflow.com/q/10903394/190829), [here](http://stackoverflow.com/q/6052337/190829) — JimmyPena, Jun 21 '12 at 17:33
@Siddharth Rout - yes, the format will always be in this fashion. — tonyf, Jun 22 '12 at 01:32

score 4 · Accepted Answer · answered Jun 21 '12 at 15:07

If the format is going to remain same then you can use this

Sub Sample()
    Dim strSample As String

    strSample = "ACOST 2012 Pricing Table - (PRJ 216664 - Financial Server Decommission) - 12 Month Term"
    strSample = Split(Split(strSample, "(")(1), ")")(0)

    Debug.Print Trim(Split(Split(strSample, "-")(0), " ")(1))
    Debug.Print Trim(Split(strSample, "-")(1))
End Sub

score 0 · Answer 2 · answered Jun 21 '12 at 15:53

Another way;

Data = "ACOST 2012 Pricing Table - (PRJ 216664 - Financial Server Decommission) - 12 Month Term"

dim re As object, matches as object: Set re = createobject("vbscript.regexp")
re.pattern="- \(.*?(\d+)\s+-\s+(.*?)\)(\s|$)"

with re.Execute(Data)
    msgBox  .item(0).SubMatches(0) & " / " & .item(0).SubMatches(1)
End with

Get the last group of digits after "- (" before a "-" surrounded by whitespace then get everything upto the next ) thats followed by whitespace or the end of the line"

Extract substring from a string in VBA

2 Answers2