Solved

regex pattern to delete a pattern i need  for forward backslash and reverse backslash

Posted on 2014-04-10
8
1,725 Views
Last Modified: 2014-04-13
vba excel 2010

The following code works for dashes -
as in previous question:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28392904.html
before:
A-1195-004-AS
AT-11-4
AT-11-400
TR-1151-404
F-16441
AK - 5230
AK- 52309 -  500
RYB- 921 - 002
VB-9211-0-5-13
RYB-922-12

After:
A1195004AS
AT114
AT11400
TR1151404
F16441
AK5230
AK52309500
RYB921002
VB92110513
RYB92212




    Static oRE As Object
'
    If oRE Is Nothing Then
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Global = True
    End If
    oRE.Pattern = "(?:([A-Z]+[0-9]*)\s*-\s*(?:([A-Z0-9]*)\s*-\s*)?(?:([A-Z0-9]*)\s*-\s*)?(?:([A-Z0-9]*)\s*-\s*)?)|([0-9]+)\s*-\s*([A-Z]+[0-9]*)(?:\s*-\s*([A-Z0-9]*))?(?:\s*-\s*([A-Z0-9]*))?"
    cD = oRE.Replace(cD, "$1$2$3$4$5$6$7$8")

Open in new window


I do not want it to delete patterns like fraction:
1/2  or 3-1/2 or 3 1/2 or 16/32


I need this regex to work for the following patterns BUT NOT in the same regex call
before:
LU250/40/H/ECO
LU310/H/ECO
CMH39/MR16/UL93\SP
CMH39/MR16/UL93/FL
CMH39/MR16/UL93WFL
F28T8/XL\SPX35\ECO
CMHi\23P38/FL/ECO

After:
LU25040HECO
LU310HECO
CMH39MR16UL93SP
CMH39MR16UL93FL
CMH39MR16UL93WFL
F28T8XLSPX35ECO
CMHi23P38FLECO

Thanks
fordraiders
0
Comment
Question by:fordraiders
  • 5
  • 3
8 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
So basically you want to strip the "/" and "\", but still want to keep them on this types:
1/2-20
1/4-20
1/4-2
1/3-35
1/8-10

Please confirm.
0
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
Comment Utility
Pattern:
"([A-Z]\w*)[/\\](\w*)[/\\](\w*)[/\\]?(\w*)[/\\]?(\w*)[/\\]?"
replacement
$1$2$3$4$5

Open in new window

works for the following:
LU250/40/H/ECO
LU310/H/ECO
CMH39/MR16/UL93\SP
CMH39/MR16/UL93/FL
CMH39/MR16/UL93WFL
F28T8/XL\SPX35\ECO
CMHi\23P38/FL/ECO
1/2-20
1/4-20
1/4-2
1/3-35
1/8-10
0
 
LVL 3

Author Comment

by:fordraiders
Comment Utility
dan, sorry yes...
to your question

keep in tact:
1/2-20
1/4-20
1/4-2
1/3-35
1/8-10

or

1/4
13/32
1/5
1-1/5
23 1/2
23-1/2

etc...
0
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 3

Author Comment

by:fordraiders
Comment Utility
ok, is this correct ?

  If oRE Is Nothing Then
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Global = True
    End If
    oRE.Pattern = "([A-Z]\w*)[/\\](\w*)[/\\](\w*)[/\\]?(\w*)[/\\]?(\w*)[/\\]?"

   cD = oRE.Replace(cD, "$1$2$3$4$5")
0
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
Looks correct.
0
 
LVL 3

Author Closing Comment

by:fordraiders
Comment Utility
Thanks working fine.
0
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
Glad I could help!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now