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?
 
aikimarkCommented:
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)President / OwnerCommented:
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
 
GRayLCommented:
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.

Advertise Here