Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-04-10
8
Medium Priority
?
3,820 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 35

Expert Comment

by:Dan Craciun
ID: 39991835
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 35

Accepted Solution

by:
Dan Craciun earned 2000 total points
ID: 39991888
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
ID: 39992119
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
Industry Leaders: 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!

 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39992143
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 39992772
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 35

Expert Comment

by:Dan Craciun
ID: 39993284
Looks correct.
0
 
LVL 3

Author Closing Comment

by:Fordraiders
ID: 39998252
Thanks working fine.
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39998255
Glad I could help!
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!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

824 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