Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    1,294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA code for string - remove all spaces

    I have a string that has spaces( space count unknown) & I want go through the string & remove all spaces.
    The spaces can be in the middle of the string.
    The result being string with no spaces.

    Does anyone have existing code which performs this function.
    TIA
    Diana

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code for string - remove all spaces

    Diana,

    It depends on what version of office you're running.

    in Office 2000 you can say
    strSomething = Replace(strSomething, " ","")

    In Office 97, you have to write your own replace function.

    I used to have a function which worked; I posted it on the old forum, and received all sorts of replies about how to do it better- my way was not very efficient at all. But it worked, and because I'm now O2K, I don't have to worry about a more efficient version



    <pre>Public Function Replace(sSourceString As String, sSearchFor As String, sReplaceWith As String) As String
    Dim lPos As Long
    Dim lLen As Long
    Dim lid As Long

    Replace = sSourceString
    lLen = Len(sSearchFor)
    If sSourceString = "" Then
    Exit Function
    End If
    lPos = InStr(Replace, sSearchFor)
    Do While lPos <> 0
    If Len(Replace) = lLen Then
    Replace = sReplaceWith
    ElseIf lPos = 1 Then
    Replace = sReplaceWith & Mid$(Replace, lPos + lLen)
    ElseIf lPos = Len(Replace) - lLen + 1 Then
    Replace = Mid$(Replace, 1, lPos - 1) & sReplaceWith
    Else
    Replace = Mid$(Replace, 1, lPos - 1) & sReplaceWith & Mid$(Replace, lPos + lLen)
    End If
    lPos = InStr(Replace, sSearchFor)
    Loop
    End Function</pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code for string - remove all spaces

    Function ShaveSpace(s As String) As String
    ShaveSpace = WorksheetFunction.Substitute(s, " ", "")
    End Function

    Another handy WorksheetFunction is Trim which removes all extra spaces, but leaves one between words.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    1,294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code for string - remove all spaces

    Hi gwhitfield

    I'm on Office97
    Your functions great it works!
    The only problem is I'm getting a syntax error on line

    'Do While lPos <> 0

    therefore I have amended to
    Do While lPos <> 0

    & it works!
    thanks alot!
    Diana

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code for string - remove all spaces

    I humbly submit my rendition of RemoveSpaces (yes, not as universal as Geoff's Replace(), but looks nice next to the devilled egg on the hors d'oeuvre tray.)

    <pre>Function RemoveSpaces(aText As String) As String
    Dim aTextOut As String
    Dim aPos As Integer
    Do
    aPos = InStr(aText, " ")
    aTextOut = aTextOut + Mid$(aText, 1, aPos - 1)
    aText = Mid$(aText, aPos + 1)
    Loop While InStr(aText, " ") <> 0
    RemoveSpaces = aTextOut + aText
    End Function</pre>

    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  6. #6
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: VBA code for string - remove all spaces

    Kevin,
    Thanks for sharing, this is good[img]/w3timages/icons/king.gif[/img].

    Now, I couldn't resist making your deletion string one of the arguments for the function rather than hard-coded (if you ever wanted to remove something other than spaces I guess).
    Also, aPos as Long rather than Integer would avoid an error if you ever passed it a string longer than 32,000 and some characters (if you ever wanted to remove all the spaces from an entire book, I guess).

    <pre>Function RemoveSpaces2(aSource As String, aRemove As String) As String
    Dim aTextOut As String
    Dim aPos As Long
    Do
    aPos = InStr(aSource, aRemove)
    aTextOut = aTextOut + Mid$(aSource, 1, aPos - 1)
    aSource = Mid$(aSource, aPos + 1)
    Loop While InStr(aSource, aRemove) <> 0
    RemoveSpaces2 = aTextOut + aSource
    End Function
    </pre>


    This thread is prompting a recollection of a similar one on Ye Olde Lounge - I think Robin Trew provided a revised version of a function posted by Chris Greaves (where are both of them?[img]/w3timages/icons/sad.gif[/img]). The difference being it did the full replace bit, with arguments for source, find and replace strings. Maybe this is the one Geoff referred to earlier in this thread.
    I'm gonna track that one down and post it here....

    Gary

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code for string - remove all spaces

    Gary,

    It's a pity MS didn't provide an intrinsic "Replace" function WWWWAAAYYY before they did. It was long overdue.

    I understand (at least) one of the aforementioned is around somewhere.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA code for string - remove all spaces

    I think this was based on code posted in the old Lounge, but it's been so long ago...

    With this, you can replace a value with an empty string to remove it.

    <pre>Function ReplaceAll(varIn As Variant, varFind As Variant, _
    varNew As Variant) As Variant
    'Created by Charlotte Foust
    'Replaces all instances of varFind in the passed varIn
    Dim intFindLen As Integer
    Dim intFindPos As Integer
    Dim varOutput As Variant

    'initialize the variables
    varOutput = varIn
    intFindLen = Len(varFind & "")
    intFindPos = 0

    If Not IsNull(varIn) Or IsNull(varFind) Then
    'If varIn contains input or if a varNew
    'will replace the existing contents,
    'See if varFind exists in varIn.
    If IsNull(varFind) Then
    varOutput = varNew
    Else
    intFindPos = InStr(varIn, varFind)
    If intFindPos > 0 Then
    'If varFind exists, replace all instances.
    Do
    varOutput = Left(varOutput, intFindPos - 1) _
    & varNew _
    & Mid(varOutput, intFindPos + intFindLen)
    intFindPos = InStr(intFindPos + 1, varOutput, varFind)
    Loop Until intFindPos = 0
    End If
    End If
    End If
    ReplaceAll = varOutput
    End Function</pre>

    Charlotte

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VBA code for string - remove all spaces

    Hi Charlotte,
    Just for my own enlightenment, why do you use Variants rather than Strings?
    Thanks,
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code for string - remove all spaces

    Thanks Gary,

    Nice touch on improving my limited function. Into the code library it goes.

    As I recall, Robin Trew moved on to a higher tower at Cambridge. Chris Greaves? I don't know. He was just starting to come into his own on the old Lounge.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    1,294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code for string - remove all spaces

    Gary

    "Also, aPos as Long rather than Integer would avoid an error if you ever passed it a string longer than 32,000 and some characters (if you ever wanted to remove all the spaces from an entire book, I guess)."


    SO thats why you define a numeric variable as long.
    I never really knew the difference between defining a a variable as lng or int.
    Therefore I have been defining my numeric vars as intergers.

    Thanks Diana

  12. #12
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code for string - remove all spaces

    Ouch. Dangerous stuff!

    We had an app which crashed badly after some months running without problems. It had just added record 32767 and incremented a counter. Ouch.

    MS's Integer is dangerous as a default. I use it far too much myself. It's too late now- but "Integer" as a 2-byte and "Short" as a one byte would have been much better.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  13. #13
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: VBA code for string - remove all spaces

    I'd also learned this one from similar painful experience.[img]/w3timages/icons/yikes.gif[/img]

    Here's an interesting quote I saved from a Robin Trew post:
    "Integers turn out to be a more or less fictional data type on 32 bit systems. The VBA compiler coerces them to longs anyway, so it may be simpler (and even marginally faster) to simply declare longs."

    If that's the case, why use Integers, eh?

    BTW I located Robin's post with the Replace function (which is where I got the above quote as well); will post it shortly.

    Gary

  14. #14
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: VBA code for string - remove all spaces

    Hi Kevin,

    I probably should have restrained my editorial impulse - it's a bad habit[img]/w3timages/icons/devil.gif[/img].

    Here's the Replace function code Robin Trew posted on the OL in October 2000. This was a suggested revision of an original posted by Chris Greaves.

    (Now I need to study this one, and Charlotte's, and try to understand what the heck they're doing!)

    <pre>Function ReplaceAll(Source As String, _
    Pattern As String, ReplaceWith As String, _
    Optional CompareMethod As VbCompareMethod = _
    vbBinaryCompare) As String
    'Posted to Woody's Lounge by Robin Trew, Oct 2000
    ' (revision of original by Chris Greaves)
    Dim strNew As String
    Dim strRest As String
    Dim lngFound As Long
    Dim lngPatternChars As Long
    strRest = Source
    lngPatternChars = Len(Pattern)
    lngFound = InStr(1, strRest, Pattern, CompareMethod)

    Do While lngFound
    strNew = strNew & _
    Left$(strRest, lngFound - 1) & ReplaceWith
    strRest = Mid$(strRest, lngFound + lngPatternChars)
    lngFound = InStr(1, strRest, Pattern, CompareMethod)
    Loop

    If Len(strRest) Then strNew = strNew & strRest
    ReplaceAll = strNew
    End Function
    </pre>


  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA code for string - remove all spaces

    Mainly because I might want to handle numbers rather than text, and this way it works nicely on either one.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •