Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 825
  • Last Modified:

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!!
0
jandrews404
Asked:
jandrews404
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now