Solved

VBA Regular Expression Help

Posted on 2011-03-18
9
794 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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.

808 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