Fordraiders
asked on
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
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*"
or
APEA near "<space>F3120<SPACE>ER3*"
or this
BREAKERS near "*<space>F3120<SPACE>ER3*"
or this
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*"
to
BREAKERS near "*F3120ER3*" near company
APEA near "<space>F3120<SPACE>ER3*"
to
APEA near "F3120ER3*"
Thanks
fordraiders
or go to Find and Replace
put <space> in find
and in replace put nothing. and click replace all
put <space> in find
and in replace put nothing. and click replace all
ASKER
professorjimjam
no i need to do this from vba code please.
thanks
fordraiders
no i need to do this from vba code please.
thanks
fordraiders
Have you tried using the VBA Replace() function? Do you have any code written yet?
ok here is the VBA code. see attached file with UDF
uploaded file.
Multiple-Substitute-UDF.xlsm
Multiple-Substitute-UDF.xlsm
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>
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
ASKER
the <space> is actual " " not the word <space>.
APEA near "<space>F3120<SPACE>ER3*"
this is actual text
APEA near " F3120 ER3*"
APEA near "<space>F3120<SPACE>ER3*"
this is actual text
APEA near " F3120 ER3*"
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
ASKER
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
Did you run the last code I provided ?
ASKER
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*"
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*"
before you did not mention that it is the text boxes.
here is the macro for 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
ASKER
"textbox on a userform " not on a sheet
sorry
sorry
how many userforms? is it just one user form? Userform1? or there are many?
ASKER
just one "userform1"
txt37 = textbox object name
but cD is the variable for the value
cD = txt37.value
txt37 = textbox object name
but cD is the variable for the value
cD = txt37.value
ASKER
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
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
ASKER
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
^([^,]*)\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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
still deleting spaces outside the quotes, but useable.
=SUBSTITUTE(A1,"<space>","