VBA code for string – remove all spaces

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Chris Greaves? He’s back

Reply | Quote

Geoff,

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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]

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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…

Reply | Quote