Replacing Multiple Spaces from the Middle of a String (1 Viewer)

RogerCooper

Registered User.
Local time
Today, 05:34
Joined
Jul 30, 2014
Messages
272
Is there a simple way of replacing multiple spaces from the middle of string with a single space? This works

Code:
Ship To Name: Replace(Replace(Replace(Replace([SHIP TO Name],"  "," "),"  "," "),"  "," "),"  "," ")

Is there a cleaner solution?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:34
Joined
Feb 19, 2002
Messages
42,724
Have you looked at the help entry for the Replace() function?
Looks like the Count argument might solve the problem.

You need to nest two Replace() functions. The Inner one converts " " (single space) to "?" or something you know will not be in the string like "?123?". The outer one replaces the first substituted character(s) for one space.

Help for Access has gotten progressively worse over the years but it is still the first place I start and I've been doing Access for 25+ years. Think of it as a sign of respect. The people who designed Access and VBA are pretty clever and they've thought of a lot of ways to solve my problems if only I ask them:)
 
Last edited:

ebs17

Well-known member
Local time
Today, 14:34
Joined
Feb 7, 2020
Messages
1,745
SQL:
SELECT
   FieldX,
   InnerTrim(FieldX) AS X1,
   RegexReplace(FieldX, " {2,}", " ") AS X2
FROM
   TableY

Code:
' in a standard module
Private pRegEx As Object

Public Property Get oRegEx() As Object
   If (pRegEx Is Nothing) Then Set pRegEx = CreateObject("Vbscript.Regexp")
   Set oRegEx = pRegEx
End Property

Public Function RegExReplace(ByVal SourceText As String, _
      ByVal SearchPattern As String, _
      ByVal ReplaceText As String, _
      Optional ByVal bIgnoreCase As Boolean = True, _
      Optional ByVal bGlobal As Boolean = True, _
      Optional ByVal bMultiLine As Boolean = True) As String
  
   With oRegEx
      .Pattern = SearchPattern
      .IgnoreCase = bIgnoreCase
      .Global = bGlobal
      .Multiline = bMultiLine
      RegExReplace = .Replace(SourceText, ReplaceText)
   End With
End Function

Public Function InnerTrim(ByVal ThisString As String) As String
    Dim sResult As String
    sResult = ThisString
    Do While InStr(1, sResult, "  ") > 0
        sResult = Replace(sResult, "  ", " ")
    Loop
    InnerTrim = sResult
End Function
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:34
Joined
Feb 19, 2002
Messages
42,724
Or, you can write your own code if you don't like to use functions that are already defined in VBA;)
 

ebs17

Well-known member
Local time
Today, 14:34
Joined
Feb 7, 2020
Messages
1,745
functions that are already defined in VBA
Multiple spaces means I know neither the number nor the position of the spaces, nor any repetitions.
Code:
? Replace("aa   aa aa      aa   aa aa", " ", "?", 1, 5)
aa???aa?aa?     aa   aa aa
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:34
Joined
Feb 19, 2002
Messages
42,724
Looks like I read the help entry wrong. Sorry. I guess the loop is the only way. Thanks @ebs17
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:34
Joined
May 21, 2018
Messages
8,417
This should work too
Code:
Public Function ReplaceMultiSpace(strIn As String) As String
  Dim old As String
  Dim newout As String
  old = Trim(strIn)
  newout = old
  Do
    old = newout
    newout = Replace(old, "  ", " ")
  Loop Until old = newout
  ReplaceMultiSpace = newout
End Function
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:34
Joined
May 7, 2009
Messages
18,987
Is there a simple way of replacing multiple spaces from the middle of string with a single space? This works
another function:
PHP:
Public Function SngSpaceOnly(Byval value As Variant)
    If IsNull(value) then
        SngSpaceOnly = value
        Exit Function
    End If
    Do While Instr(1, value, "  ") <> 0
        value = Replace$(value, "  ", " ")
    Loop
    SngSpaceOnly=value
End Function
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:34
Joined
Jan 20, 2009
Messages
12,840
So much complexity suggested to do something so simple! No loops required.
You want to keep the first space and remove all those after it.

Code:
Replace(value, " ", "", Instr(value, " ") + 1)
 

isladogs

MVP / VIP
Local time
Today, 13:34
Joined
Jan 14, 2017
Messages
18,129
So much complexity suggested to do something so simple! No loops required.
You want to keep the first space and remove all those after it.

Code:
Replace(value, " ", "", Instr(value, " ") + 1)

I have for years looped through using code similar to that suggested by @MajP

I agree with removing complexity but you're not testing for the same thing here.
I believe the OP wants to change strings like "abc def ghi xyz" to "abc def ghi xyz" which isn't what your code does


1671700665535.png
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:34
Joined
Jan 20, 2009
Messages
12,840
If you need to preserve other single spaces in the string then this one will return all the characters outside of the multiple spaces. assuming that there is a single case of multiple spaces in the string.

Code:
 Left(value, Instr(value, "  ") - 1 & Mid(value, InstrRev(value, "  ") + 2)
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:34
Joined
Jan 20, 2009
Messages
12,840
I believe the OP wants to change strings like "abc def ghi xyz" to "abc def ghi xyz"
OK multiple cases is a different kettle of fish and more complex but I still would not do it with a loop.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:34
Joined
May 21, 2018
Messages
8,417
My assumption is you need to handle two or more spaces. " Abc __________ def"
 

ebs17

Well-known member
Local time
Today, 14:34
Joined
Feb 7, 2020
Messages
1,745
I also showed an approach with regular expressions above. This one doesn't need a loop either, because patterns are used for everything.

For one or the other, the use of an additional external object may seem a bit exaggerated.,
- But you will not experience any significant speed disadvantage with normal amounts of data.
- A high level of variability and flexibility can be achieved even with minor changes to the search pattern. The approach shown is not specialized and limited to any particular task, but an entry point to a vast world of possibilities.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:34
Joined
Jan 20, 2009
Messages
12,840
I also showed an approach with regular expressions above. This one doesn't need a loop either, because patterns are used for everything
Colin (isladogs) did some speed comparisons on Regex a while back and the performance was surprisingly poor.
I think this might be it.

I don't have Access at home any more so I can't test the engine centric approach I'm thinking about.
It would be something along the lines of what I described here.
 

ebs17

Well-known member
Local time
Today, 14:34
Joined
Feb 7, 2020
Messages
1,745
some speed comparisons on Regex
However, one should avoid the mistake of creating the RegEx object in a function each time, instead one would use a persistent object. I like using RegEx-using functions, also in queries (so suitable for the masses). With the persistent object, there was an acceleration by a factor of about 11 in own comparisons in specific queries.

When I compare and measure, I should also look at what I'm using. The VBA library does not have to be reloaded every time its functions are used.
 

isladogs

MVP / VIP
Local time
Today, 13:34
Joined
Jan 14, 2017
Messages
18,129
My earlier article on testing the speed of RegEx compared to other approaches where available can be found at:

I did indeed find RegEx to be significantly slower in the tests done at that time but in my conclusions, I wrote:
Regular expressions are a very powerful tool for developers to make use of.
However, in this particular set of tests, using RegEx was clearly disadvantageous.
Perhaps its use was overkill for the test done?

In other cases, Regex may provide the best or only method of obtaining results.

I would be grateful for any feedback on this article
I would also welcome any suggestions for other tests in order to further assess the comparative strength of regular expressions against other methods.

This thread may just have provided a suitable set of tests:
I compared the speed of the code suggested by @MajP, @arnelgp & @ebs17. I discarded @Galaxiom's code as it doesn't give the same output

This is the module code I used including the comparative tests.
I looped through each test 10000 times as all are very fast for such a simple task:

Code:
Option Compare Database
Option Explicit

' in a standard module
Private pRegEx As Object

Public Property Get oRegEx() As Object
   If (pRegEx Is Nothing) Then Set pRegEx = CreateObject("Vbscript.Regexp")
   Set oRegEx = pRegEx
End Property

Public Function RegExReplace(ByVal SourceText As String, _
      ByVal SearchPattern As String, _
      ByVal ReplaceText As String, _
      Optional ByVal bIgnoreCase As Boolean = True, _
      Optional ByVal bGlobal As Boolean = True, _
      Optional ByVal bMultiLine As Boolean = True) As String
 
   With oRegEx
      .Pattern = SearchPattern
      .IgnoreCase = bIgnoreCase
      .Global = bGlobal
      .Multiline = bMultiLine
      RegExReplace = .Replace(SourceText, ReplaceText)
   End With
End Function

Public Function InnerTrim(ByVal ThisString As String) As String
    Dim sResult As String
    sResult = ThisString
    Do While InStr(1, sResult, "  ") > 0
        sResult = Replace(sResult, "  ", " ")
    Loop
    InnerTrim = sResult
End Function

Public Function ReplaceMultiSpace(strIN As String) As String
  Dim old As String
  Dim newout As String
  old = Trim(strIN)
  newout = old
  Do
    old = newout
    newout = Replace(old, "  ", " ")
  Loop Until old = newout
  ReplaceMultiSpace = newout
End Function

Public Function SngSpaceOnly(ByVal value As Variant)
    If IsNull(value) Then
        SngSpaceOnly = value
        Exit Function
    End If
    Do While InStr(1, value, "  ") <> 0
        value = Replace$(value, "  ", " ")
    Loop
    SngSpaceOnly = value
End Function

Function SpeedTest(strIN As String)
    Dim strOUT As String, dblStart As Double, dblEnd As Double
    Dim lngCount As Long
    
    dblStart = Timer
    For lngCount = 1 To 10000
        strOUT = ReplaceMultiSpace(strIN)
    Next
    dblEnd = Timer
    Debug.Print "1. ReplaceMultiSpace" & " : Time Taken = " & dblEnd - dblStart & " s"
    
    dblStart = Timer
    For lngCount = 1 To 10000
        strOUT = SngSpaceOnly(strIN)
    Next
    dblEnd = Timer
    Debug.Print "2. SngSpaceOnly" & " : Time Taken = " & dblEnd - dblStart & " s"
    
    dblStart = Timer
    For lngCount = 1 To 10000
        strOUT = RegExReplace(strIN, " {2,}", " ")
    Next
    dblEnd = Timer
    Debug.Print "3. RegExReplace" & " : Time Taken = " & dblEnd - dblStart & " s"
    
End Function

This was the first set of tests on a short string

Code:
Sub Test1()
    Dim strIN As String
    strIN = "abc    def ghi  xyz"
    
    Debug.Print "Test1 - Short String" & vbCrLf & "===================="
    SpeedTest (strIN)    
    Debug.Print "" & vbCrLf

End Sub

The results were:

Code:
Test1 - Short String
====================
1. ReplaceMultiSpace : Time Taken = 0.0703125 s
2. SngSpaceOnly : Time Taken = 0.0703125 s
3. RegExReplace : Time Taken = 0.0859375 s

The 2 loops wgave the same results but Regex was noticeably slower

So I repeated on a much longer string using the above quote for convenience with lots of random additional spaces...

Code:
Sub Test2()

    Dim strIN As String

    strIN = "Regular   expressions are a     very powerful  tool for developers  to make use of   ." & _
    "However,   in  this particular set   of tests, using   RegEx   was   clearly  disadvantageous." & _
    "Perhaps   its use  was  overkill  for  the  test   done?" & _
    "    " & _
    "In  other cases,  Regex may  provide the best   or only   method of obtaining results." & _
    "  " & _
    "I   would be   grateful for any  feedback on   this  article" & _
    "I would also welcome   any     suggestions   for other tests  in order to further  assess the  comparative" & _
    "     strength of regular     expressions against  other    methods."
    
    Debug.Print "Test2 - Longer String" & vbCrLf & "======================"
    
    SpeedTest (strIN)
    
    Debug.Print "" & vbCrLf
End Sub

This time the results were very different:
Code:
Test2 - Longer String
======================
1. ReplaceMultiSpace : Time Taken = 0.4765625 s
2. SngSpaceOnly : Time Taken = 0.58203125 s
3. RegExReplace : Time Taken = 0.16015625 s

This seems to confirm my comments in that article.
For a simple task, using Regex may be overkill and offers no speed benefits
However, for a complex task, Regex does appear to have significant advantages.

My suspicion is that the benefits of Regex would become increasingly apparent for a much longer string

Thanks to all. This may get added to my web article
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:34
Joined
May 7, 2009
Messages
18,987
a little bit faster:
Code:
Public Function OneSpaceOnly(ByVal var As Variant)
    Static oReg As Object
    Dim s As String
    On Error GoTo create_object
    If IsNull(var) Then Exit Function
    s = var
    With oReg
        .Pattern = " {2,}"
        .Global = True
        s = .Replace(s, " ")
    End With
    OneSpaceOnly = s
    Exit Function
create_object:
    Set oReg = CreateObject("vbscript.regexp")
    Resume Next
End Function
forgot the evidence:
test1.png

test2.png


my own conclusion, with the right code, RegExp can be very fast.
 
Last edited:

Users who are viewing this thread

Top Bottom