special case to remove space in a string pattern

vba 2010 excel


I sometimes have a 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

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


or this

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

or this

BREAKERS near "*<space>F3120<SPACE>ER3*"  or  BREAKERS near "<space>F3120<SPACE>ER3*"
or
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

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

to

BREAKERS near "*F3120ER3*" near company


APEA near "<space>F3120<SPACE>ER3*"

to

APEA near "F3120ER3*"



Thanks
fordraiders
LVL 3
FordraidersAsked:
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.

ProfessorJimJamCommented:
assuming your data is in Colimn A put the following formula and drag down.

=SUBSTITUTE(A1,"<space>","")
0
ProfessorJimJamCommented:
or go to Find and Replace

put <space>   in find
and in replace put nothing. and click replace all
0
FordraidersAuthor Commented:
professorjimjam
no i need to do this from vba code please.

thanks
fordraiders
0
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.

Anthony BerenguelCommented:
Have you tried using the VBA Replace() function? Do you have any code written yet?
0
ProfessorJimJamCommented:
ok here is the VBA code.  see attached file with UDF
0
ProfessorJimJamCommented:
0
ProfessorJimJamCommented:
that was the UDF style which was case sensitive and here is the Procedure style
which is not case sensitive.  it will clear from all cells of the activesheet the string of <space>
Option Explicit
Option Compare Text


Sub DeleteWord()
Dim Cel As Range, Range As Range
Dim Word As String
    Set Range = ActiveSheet.UsedRange '.
    Word = "<space>"
        
    Application.ScreenUpdating = False
    For Each Cel In Range
        If Cel Like "*" & Word & "*" Then
            Cel = Replace(Cel, Word, "")
           'To remove the double space that follows ..
            Cel = Replace(Cel, "  ", " ")
        End If
    Next Cel
    Application.ScreenUpdating = True
End Sub

Open in new window

0
FordraidersAuthor Commented:
the  <space>  is actual   " "   not the word  <space>.

 

APEA near "<space>F3120<SPACE>ER3*"

this is actual text
APEA near " F3120 ER3*"
0
ProfessorJimJamCommented:
then in this case use this

Option Explicit
Option Compare Text


Sub DeleteWord()
Dim Cel As Range, Range As Range
Dim Word As String
    Set Range = ActiveSheet.UsedRange '.
    Word = " "
        
    Application.ScreenUpdating = False
    For Each Cel In Range
        If Cel Like "*" & Word & "*" Then
            Cel = Replace(Cel, Word, "")
           'To remove the double space that follows ..
            Cel = Replace(Cel, "  ", " ")
        End If
    Next Cel
    Application.ScreenUpdating = True
End Sub

Open in new window

0
FordraidersAuthor Commented:
jimjam,  sorry, but this still does not solve the issue. because you are taking the whole cell and not just the part i need the "space" taken out of
0
ProfessorJimJamCommented:
Did you run the last code I provided ?
0
FordraidersAuthor Commented:
sorry for the confusion
i'm not removing anything from the cells themselves.

I'm taking the cell text  value, to a textbox on a userform then taking that textbox value and passing it to a variable in my routine.

cD = txtBox37

example: per my question.

cD = APEA near " F3120 ER3*"
0
ProfessorJimJamCommented:
before you did not mention that it is the text boxes.

here is the macro for the text boxes

Sub ReplaceSpace()

' Also included the groupitems
    Dim shp As Shape
    Dim sOld As String
    Dim sNew As String

    'Change as desired
    sOld = " "
    sNew = ""
    On Error Resume Next
    For Each shp In ActiveSheet.Shapes
        With shp.TextFrame.Characters
            .Text = Application.WorksheetFunction.Substitute( _
              .Text, sOld, sNew)
        End With
        For Each gri In shp.GroupItems
        With gri.TextFrame.Characters
            .Text = Application.WorksheetFunction.Substitute( _
              .Text, sOld, sNew)
        End With
        Next
    Next
End Sub

Open in new window

0
FordraidersAuthor Commented:
"textbox on a userform "  not on a sheet

sorry
0
ProfessorJimJamCommented:
how many userforms?  is it just one user form?   Userform1?   or there are many?
0
FordraidersAuthor Commented:
just one "userform1"
txt37 =  textbox object name

but cD is the variable for the value

cD  =  txt37.value
0
FordraidersAuthor Commented:
the only part i'm concerned about getting rid of the spaces is the between the double quotes.

BREAKERS near "* F3120 ER3*" near company
is
BREAKERS near "*F3120ER3*" near company

or something like:
cD = APEA near " F3120 ER3*"

to

cD = APEA near "F3120ER3*"

Thanks for your patience
0
FordraidersAuthor Commented:
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
ProfessorJimJamCommented:
here you go , see attached excel file.
F--UserForm-Find-Replace-VBA.xlsb
0

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:
still deleting spaces outside the quotes, but useable.
0
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
Microsoft Excel

From novice to tech pro — start learning today.