VBA Regular Expression Help

Looking for some help with VBA regular expressions. My input string is in the following format

1234567890 - JOHN DOE (123456789)

I am trying to extract "JOHN DOE" from the input string. Any help is greatly appreciated!!
LVL 1
jandrews404Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
aikimarkConnect With a Mentor Commented:
Another way to parse this is to use the Split() function, first by the hyphen character and then by the open parenthesis character.

The resulting string is trimmed of any leading/trailing spaces.
Example:

myStr = "1234567890 - JOHN DOE (123456789)"
?Trim(Split(Split(myStr,"-")(1),"(")(0))

Open in new window

0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
BTW, if that's a fixed string in terms of format, then the Mid() function will do the trick nicely.  The regular expressions are for searching strings when the format is not consistent.

  If you need more details on anything, just let me know.

JimD.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Start Now
 
GRayLConnect With a Mentor Commented:
Try this in the Immediate Pane - Alt+F11

myStr = "1234567890 - JOHN DOE (123456789)"
? Mid(myStr,Instr(myStr,"-")+2,Instr(myStr,"(")-2 - (Instr(myStr,"-")+1))
JOHN DOE

0
 
GRayLCommented:
I like aikimark's much better - an elegant solution - three call to Split and Trim versus 4 calls to Mid and Instr.  It should be faster.
0
 
TSSTJeffCommented:
the actual regex can be \x22(?<Number>.*?)\s\-\s(?<Name>.*?)\(?<Number2>.*?)\)
0
 
aikimarkCommented:
@jandrews404

Do you need any help implementing our proposed solutions?
0
 
jandrews404Author Commented:
Sorry for the delayed response. Thanks for your help!
0
 
GRayLCommented:
thanks, glad to help - inelegance notwithstanding ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.