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
– Siddharth Rout, Commented Jun 21, 2012 at 14:59
Numerous related questions already exist on SO. Ex: here, here, here — JimmyPena
– JimmyPena, Commented Jun 21, 2012 at 17:33
@Siddharth Rout - yes, the format will always be in this fashion. — tonyf
– tonyf, Commented Jun 22, 2012 at 1:32

Siddharth Rout · Accepted Answer · 2012-06-21 15:07:26Z

4

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

answered Jun 21, 2012 at 15:07

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

Comments

Alex K. · Accepted Answer · 2012-06-21 15:53:09Z

0

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"

answered Jun 21, 2012 at 15:53

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Collectives™ on Stack Overflow

Extract substring from a string in VBA

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related