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!
Course Of The Month
4 days, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
VBA Regular Expression Help
Posted on 2011-03-18
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!!
1234567890 - JOHN DOE (123456789)
I am trying to extract "JOHN DOE" from the input string. Any help is greatly appreciated!!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3
2
2- +2
9 Comments
Active today
Excellent article on regular expressions here:
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html
Which should fill in the blanks for you.
JimD.
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html
Which should fill in the blanks for you.
JimD.
Active today
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.
If you need more details on anything, just let me know.
JimD.
Try this in the Immediate Pane - Alt+F11
myStr = "1234567890 - JOHN DOE (123456789)"
? Mid(myStr,Instr(myStr,"-")
JOHN DOE
myStr = "1234567890 - JOHN DOE (123456789)"
? Mid(myStr,Instr(myStr,"-")
JOHN DOE
Active today
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:
The resulting string is trimmed of any leading/trailing spaces.
Example:
myStr = "1234567890 - JOHN DOE (123456789)"
?Trim(Split(Split(myStr,"-")(1),"(")(0))
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.
Featured Post
Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.
Suggested Courses
Course of the Month4 days, 19 hours left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.