Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2029
  • Last Modified:

Access VBA: replace spaces in string

Hi,

I would like to take a string such as "Hello World,  Opps I just added 2 spaces after the comma" and replace it with "Hello+World,+Opps+I +just+added+2+spaces+after+the+comma"

The problem with the Replace() function is that it replaces sequential spaces so I get "Hello+World,++Opps". Thanks
0
BradleyOnTheRoad
Asked:
BradleyOnTheRoad
  • 3
1 Solution
 
MnInShdwCommented:

Sub test3()
    
    Dim s As String
    Dim i As Integer
    Dim Result, currentletter As String
    
    
    s = "Hello World,  Opps I just added 2 spaces after the comma"
    
    For i = 1 To Len(s)
        currentletter = Mid(s, i, 1)
        If currentletter = " " Then currentletter = "+"
        Result = Result & currentletter
        If currentletter = "+" And Mid(s, i + 1, 1) = " " Then i = i + 1
    Next
    
    Debug.Print Result
    
End Sub

Open in new window

0
 
rgn2121Commented:
Couldn't you just do a replace for the double space first?

If currentletter = "  " Then currentletter = "+"     'Replaces the double space

code....

If currentletter = " " Then currentletter = "+"   'Replaces all single spaces...
0
 
rgn2121Commented:
oops...I just glanced up and thought that was part of the askers code...I didn't even look through it.  Sorry about that!
0
 
rgn2121Commented:
I guess what I really meant was....

dim tempString as string
tempString = "Hello World,  Opps I just added 2 spaces after the comma"

tempString = Replace(tempString, "  ", "+")  'Replace the double space
temptString = Replace(tempString, " ", "+")  ' Replace the single Space
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now