Solved

regex expression needed to capture unique  string variable and get rid of blank whitespace or any type of space

Posted on 2014-10-03
10
145 Views
Last Modified: 2014-10-07
vba 2010 excel

Dim Cd  as string

APEA,  BREAKERS, COMPANY  could be any text, not just those specific names.
<space>   is a literal space in the string

I sometimes have a variable string that is going into a sql statement.

I need to check for a unique pattern and remove the "spaces" in that pattern before going to that sql statement.

the string may look like this

Cd = "*<space>F3120<SPACE>ER3*" NEAR APEA
or  
Cd = APEA near "<space>F3120<SPACE>ER3*"


or this

Cd = BREAKERS near "*<space>F3120<SPACE>ER3*" near company

or this

Cd = BREAKERS near "*<space>F3120<SPACE>ER3*"  or  BREAKERS near "<space>F3120<SPACE>ER3*"
or
Cd = BREAKERS near "*<space>F3120<SPACE>ER3"
==============================================================


basically the  * will either be present
==========================================================
What I need:

to remove the <space> inside the string
but keep the other part of the string

Cd =  BREAKERS near "*<space>F3120<SPACE>ER3*" near company

to

Cd =  BREAKERS near "*F3120ER3*" near company


Cd = APEA near "<space>F3120<SPACE>ER3*"
actual string  example   Cd = APEA near " F3120 ER3*"

to

Cd = APEA near "F3120ER3*"

Thanks
fordraiders
0
Comment
Question by:fordraiders
  • 6
  • 4
10 Comments
 
LVL 26

Accepted Solution

by:
wilcoxon earned 500 total points
ID: 40360890
This appears to be VBA.  I am not familiar with their regex engine.  Based on the docs, this should work:
Imports System.Text.RegularExpressions
Dim Cd As String = "..."
Dim pattern As String = """(.*)"""
Dim capture as New Regex(pattern)
Dim m as Match = capture.Match(Cd)
Dim str as String = m.Groups(1).Captures(0).ToString()
Cd = capture.Replace(Cd, "")
Dim rgx As New Regex("\s+")
Dim result As String = rgx.Replace(str, "")
Dim rx As New Regex(""""&str&"""")
Dim final as String = rx.Replace(Cd,result)

Open in new window

0
 
LVL 3

Author Comment

by:fordraiders
ID: 40361819
Thanks but it is not vba.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40362107
basically trying to find a "space"  between a  starting  literal *  and ending  literal * ?

(\*)(\s)(\*)  

?

thanks
 fordraiders
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 40362141
It's .Net but, based on the docs I found, it seemed like it should be usable in VBA.  VBA regex engine seems to be very limited.  You'll probably need to do it the old-fashioned way.  Don't you need to find space within quotes (and not within asterisks as the asterisks are optional)?
dim Cd as string = "APEA near "" F3120 ER3*"""
dim start as int = instr(Cd, """) + 1
dim end as int = instr(start, Cd, """)
; the below is to deal with possible trailing "near xxx" which is in a few of your examples
; you can remove this section if the section you want to remove spaces from will always be the end of Cd
dim tmp as string = mid(Cd, end, len(Cd)-end+1)
Cd = left(Cd, end)
; end block
Cd = Replace(Cd, " ", "", start)
Cd = Cd & tmp

Open in new window

0
 
LVL 3

Author Comment

by:fordraiders
ID: 40362566
Yes...either way is ok.  But .vba syntax is not the same with this code
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 26

Expert Comment

by:wilcoxon
ID: 40362888
The second method should work in VBA except possibly for the quote escapes (I *HATE* the way Excel and VBA escape quotes).  If it doesn't work, which part is invalid?
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40363147
(?<q>[""]).*?\k<q>

this pattern will find  stuff between the  quotes

now i just need it to replace the space in between the quotes
(?<q>[""]).*?\k<q>


APEA near "F312 0ER3*"
will return:

"F312 0ER3*"
now i just the space replaced  : so it looks like

"F3120ER3*"
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40363881
well some success...

^([^,]*)\s(.*)$

replace pattern:
$1$2
will work on 1 space

I'am using regex tester.
https://www.myregextester.com/index.php


For multiple spaces:
THIS WILL NOT WORK ?
^([^,]*)\s+(.*)$
replace pattern:
$1$2
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 40364263
Yep.  That's why I suggested doing it the "old" way and not using regex.  I don't think there is any way to replace multiple spaces in a part of a string with a regex (at least not without it getting very ugly).

Did you try the non-regex method I posted?  Did it work?  If not, what issue occurred?
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 40366231
i did not exactly use that code but, revamped mine to at least extract the information between the quotes and then additional code to just get rid of spaces in string.

Dim result As String
Dim cd2 As String
Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
' GIVE ME JUST THE QUOTES
RE.Pattern = """([^""""\\]*(?:\\.[^""""\\]*)*)"""


RE.Global = True
RE.IgnoreCase = True
Set allMatches = RE.Execute(Cd)

If allMatches.Count <> 0 Then
    result = allMatches.Item(0).SubMatches.Item(0)
End If

cd2 = result

' then do this
If oRE Is Nothing Then
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Global = True
    End If
    oRE.Pattern = "\s+"
   
     
    Cd2 = oRE.Replace(Cd2, "")
Cd2 = Cd2
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I have been reconstructing a PHP-based application that has grown into a full blown interface system over the last ten years by a developer that has now gone into business for himself building websites. I am not incredibly fond of writing PHP code o…
Whatever be the reason, if you are working on web development side,  you will need day-today validation codes like email validation, date validation , IP address validation, phone validation on any of the edit page or say at the time of registration…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

708 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

13 Experts available now in Live!

Get 1:1 Help Now