Solved

VBA Regular Expression Help

Posted on 2011-03-18
9
807 Views
Last Modified: 2012-05-11
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
Comment
Question by:jandrews404
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 57
ID: 35166250
0
 
LVL 57
ID: 35166286
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
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 250 total points
ID: 35167175
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 45

Accepted Solution

by:
aikimark earned 250 total points
ID: 35176859
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
 
LVL 44

Expert Comment

by:GRayL
ID: 35211042
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
 
LVL 1

Expert Comment

by:TSSTJeff
ID: 35279368
the actual regex can be \x22(?<Number>.*?)\s\-\s(?<Name>.*?)\(?<Number2>.*?)\)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35279407
@jandrews404

Do you need any help implementing our proposed solutions?
0
 
LVL 1

Author Closing Comment

by:jandrews404
ID: 35403950
Sorry for the delayed response. Thanks for your help!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35404011
thanks, glad to help - inelegance notwithstanding ;-)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question