Solved

special case to remove space in a string pattern

Posted on 2014-10-02
20
104 Views
Last Modified: 2014-10-06
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
0
Comment
Question by:fordraiders
  • 10
  • 9
20 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40357319
assuming your data is in Colimn A put the following formula and drag down.

=SUBSTITUTE(A1,"<space>","")
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40357327
or go to Find and Replace

put <space>   in find
and in replace put nothing. and click replace all
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40357357
professorjimjam
no i need to do this from vba code please.

thanks
fordraiders
0
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 40357369
Have you tried using the VBA Replace() function? Do you have any code written yet?
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40357371
ok here is the VBA code.  see attached file with UDF
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40357373
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40357381
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
 
LVL 3

Author Comment

by:fordraiders
ID: 40357554
the  <space>  is actual   " "   not the word  <space>.

 

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

this is actual text
APEA near " F3120 ER3*"
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40357579
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
 
LVL 3

Author Comment

by:fordraiders
ID: 40358729
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40358745
Did you run the last code I provided ?
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40358770
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
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40359915
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
 
LVL 3

Author Comment

by:fordraiders
ID: 40360453
"textbox on a userform "  not on a sheet

sorry
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40360466
how many userforms?  is it just one user form?   Userform1?   or there are many?
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40360498
just one "userform1"
txt37 =  textbox object name

but cD is the variable for the value

cD  =  txt37.value
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40360508
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
 
LVL 3

Author Comment

by:fordraiders
ID: 40363885
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 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 40364281
here you go , see attached excel file.
F--UserForm-Find-Replace-VBA.xlsb
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 40365242
still deleting spaces outside the quotes, but useable.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
First Blank Cell in a range 7 34
Adding Text that self adjusts in a Cell 8 32
Formula 6 46
Consolidate xl 2010 worksheets with text 2 23
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

910 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

24 Experts available now in Live!

Get 1:1 Help Now