News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • VBA code for string – remove all spaces

    Posted on WSDiana Comment on the AskWoody Lounge

    Home Forums AskWoody support Productivity software by function Visual Basic for Applications VBA code for string – remove all spaces

    Viewing 2 reply threads
    • Author
      Posts
      • #351918 Reply
        WSDiana
        AskWoody Lounger

        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

      • #510543 Reply
        WSgwhitfield
        AskWoody Lounger

        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

        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
        • #510578 Reply
          WScri
          AskWoody Lounger

          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.

        • #510663 Reply
          WSDiana
          AskWoody Lounger

          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

        • #511463 Reply
          WSKevin
          AskWoody Lounger

          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.)

          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
          • #511523 Reply
            WSGary Frieder
            AskWoody Lounger

            Kevin,
            Thanks for sharing, this is good.

            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).

            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
            

            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?). 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

            • #511525 Reply
              WSgwhitfield
              AskWoody Lounger

              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.

            • #511543 Reply
              WScharlotte
              AskWoody Lounger

              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.

              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
              • #511546 Reply
                WSrory
                AskWoody Lounger

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

              • #511636 Reply
                WScharlotte
                AskWoody Lounger

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

            • #511560 Reply
              WSKevin
              AskWoody Lounger

              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.

              • #511622 Reply
                WSGary Frieder
                AskWoody Lounger

                Hi Kevin,

                I probably should have restrained my editorial impulse – it’s a bad habit.

                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!)

                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
                
              • #511721 Reply
                WSKevin
                AskWoody Lounger

                Gary,

                Robin was (is) something else, I must say.

                But, why, if you don’t mind a bit more thinking on this subject, did he declare the optional parameter CompareMethod defined as a vbCompareMethod but assigned a value of vbBinaryCompare (which is 0)? Does not seem to possess any variable characteristics if it’s defined to a value of 0 (vbBinaryCompare constant = 0). Why not leave that off the definition and use instr(1,strRest, Pattern, vbBinaryCompare) in the function body?

                Robin’s intellect (and probably yours too) exceeds my own, so I’d not be surprised if you say “Because, bla bla bla”. And then I’ll get it and say “Ahhh”.

              • #511755 Reply
                WSGary Frieder
                AskWoody Lounger

                Hi Kevin,

                Because, I think, it was a mistake?!
                You’re right, it shouldn’t have had a value assigned.
                (And if there’s some obscure reason beyond that, Robin’s not here to explain it.)

                I readily agree Robin’s intellect is on a plane of its own; my own intellect has a very spotty attendance record so you have to catch me on a good day!

                Gary

              • #514164 Reply
                WSgwhitfield
                AskWoody Lounger

                Chris Greaves? He’s back

              • #514168 Reply
                WSKevin
                AskWoody Lounger

                Geoff,

                Thanks. I just checked in with him to say Hi!

            • #511598 Reply
              WSDiana
              AskWoody Lounger

              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

              • #511609 Reply
                WSgwhitfield
                AskWoody Lounger

                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.

              • #511615 Reply
                WSGary Frieder
                AskWoody Lounger

                I’d also learned this one from similar painful experience.

                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

              • #511644 Reply
                WSgwhitfield
                AskWoody Lounger

                Gary,

                I’m not quite about integers being coerced to to longs by the VBA compiler. VBA code still crashes with and integer value > 32767- or did I misunderstand what you were saying?

              • #511661 Reply
                WSGary Frieder
                AskWoody Lounger

                Edited by Gary Frieder on 01/01/25 10:04.

                Geoff,

                Well I thought I understood what that meant, now I realize that I don’t.

                I’ve just sent a brief note to Robin asking for a little more detail on this. If we’re lucky we’ll get an explanation from the master; if not I’ll have to do some more digging for info.

                Gary

                [Just found the following relevant reference from MS Press’ MS Office 2000 Visual Basic Programmer’s Guide:
                [indent]


                “… since variables of type Integer are converted to variables of type Long, it makes sense to declare variables that will store integer values as type Long instead of as type Integer”


                [/indent]
                I don’t know how the compiler works, but a plausible guess might be that by declaring the variable as an Integer type, the capacity limit of an Integer type is invoked, and even though the Integer is then converted to a Long, essentially half of the capacity of the Long is unused and unavailable(?) – just a guess pending a real answer.

              • #511752 Reply
                WSGary Frieder
                AskWoody Lounger

                Geoff,

                I’ve heard back from Robin – who is alive and well, but harried – running a university languages department (not computer languages!).
                Here was his take on the question, which is not too different from my surmise:

                [indent]


                In haste (time presses, alas, these days) we would need to speak to the compiler-writers to get the details, but my impression is that while the compiler still masks or does error checking to trap out-of-range values (out of integer range), there are no space-saving or speed advantages, as the data is actually held in a long (the machine registers are 32 bit).


                [/indent]

              • #511756 Reply
                WSgwhitfield
                AskWoody Lounger

                OK, thanks Gary.

                I still have a beef though for allowing such a limited variable type to be an implied default. “Long” should have been called “Integer”- and “Integer” “Short”. It would save the torn-out hair of many a new programmer.

              • #511763 Reply
                WScharlotte
                AskWoody Lounger

                Ever worked with SQL Server? Then you get BigInt and Int and TinyInt with nary a Long in sight!

              • #511836 Reply
                WSPeterC
                AskWoody Lounger

                One important use for the Integers is when creating records with the Type statement, especially when these have to exactly match C structs when calling Win32 API or other DLL functions. In these cases its essential to have a 2-byte value, but for purely VBA coding I always use Longs.

                Peter Cooper

              • #511899 Reply
                WScharlotte
                AskWoody Lounger

                Actually, I generally do also unless the numbers are specifically restricted to the integer range. Then I use integers as a sort of reminder to myself of the allowable range of values.

      • #511664 Reply
        WSgwhitfield
        AskWoody Lounger

        Hi,

        What I’m saying is the an integer has an upper limit of 32767 (or round about). I think that’s because it stores an integer in what I would call (in my mainframe terminology) bytes. From memory, a word used to be 2 bytes (but they were Octal bytes- I think- in the old days, now they’re hex).

        It doesn’t really matter so much any more. The main thing is that Integers in VB and VBA are limited and limiting- and in many cases misleading and dangerous. They were useful in the early days of VB when memory was so limited- but I think have outlived their usefulness.

        Some thoughts only…

    Viewing 2 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: VBA code for string – remove all spaces

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.