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

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
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*"
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


Cd =  BREAKERS near "*F3120ER3*" near company

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


Cd = APEA near "F3120ER3*"

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
Thanks but it is not vba.
FordraidersAuthor Commented:
basically trying to find a "space"  between a  starting  literal *  and ending  literal * ?



Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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

FordraidersAuthor Commented:
Yes...either way is ok.  But .vba syntax is not the same with this code
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?
FordraidersAuthor Commented:

this pattern will find  stuff between the  quotes

now i just need it to replace the space in between the quotes

APEA near "F312 0ER3*"
will return:

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

FordraidersAuthor Commented:
well some success...


replace pattern:
will work on 1 space

I'am using regex tester.

For multiple spaces:
replace pattern:
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?
FordraidersAuthor Commented:
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")
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Regular Expressions

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.