Solved

Access VBA: replace spaces in string

Posted on 2008-10-17
4
2,006 Views
Last Modified: 2013-11-27
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
Comment
Question by:BradleyOnTheRoad
  • 3
4 Comments
 
LVL 1

Accepted Solution

by:
MnInShdw earned 500 total points
ID: 22747242

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
 
LVL 12

Expert Comment

by:rgn2121
ID: 22747247
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
 
LVL 12

Expert Comment

by:rgn2121
ID: 22747252
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
 
LVL 12

Expert Comment

by:rgn2121
ID: 22747257
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question