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
151 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
[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
  • 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
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 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
 
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
REGEX 16 59
Need help modifying regex expression 3 72
Validate textbox with client generated button 6 80
extracting data from a variable regular expression 2 46
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…
Do you hate spam? I do, and I am willing to bet you do as well. I often wonder, though, "if people hate spam so much, why do they still post their email addresses on the web?" I'm not talking about a plain-text posting here. I am referring to the fa…
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…

734 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