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.

Answer 1

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