Solved

Access VBA: replace spaces in string

Posted on 2008-10-17
4
2,011 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

739 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