Help with removing spaces in a string 1

[meckeard]

Hi All,

I have a string like this:

100 Some Street Newark DE 19999

I need to remove any empty spaces that are 2 or more in length and replace them with 1 empty space. So the above example would look like this:

100 Some Street Newark DE 19999 (there should only be 1 space between each word or value)

The string is actually split into an array, but since I use a blank space to split the string, I would get an array that has a lot of empty items due to the number of spaces between the state & zip value.

One thought was to loop thru the array and remove any balnk of null items. But I don't know the code to remove an item from the array.

Additionally, this would mean that I would have to loop thru every array. And there will be a lot of records.

Is there a way to strip out the multi empty spaces before I split the string into the array?

Thanks,
Mark

 

[CajunCenturion]

Have you tried using the Replace function?

Replace(string, " ", " ")


Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[meckeard]

Opps, sorry. I should have mentioned that I am using Access 97. I don't think it supports the Replace function.

Mark

 

[Craig0201]

It does if you look in FAQs for a VBA5 version of Replace.

Craig

 

[EBGreen]

Cajun, I think that replace would need to be wrapped in a While loop to continue doing replaces until there are no more instances of double spaces. Otherwise, an instance of three spaces would become two spaces.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[EBGreen]

You could alternatively use a Regular Expression Object to replace any number of spaces with a single space.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[CajunCenturion]

TomThumbKP - perhaps, but it's a moot point since in A97.

meckeard - Since you have the string already split into an array, you can recombine the string with a loop like the following:

NewStr = Array(LBound(Array))
For Idx = LBound(Array) + 1 to UBound(Array)
If (Len(Trim(Array(Idx))) > 0) Then
NewStr = NewStr & " " & Trim(Array(Idx))
End If
Next Idx

Where Array is the Name of your array.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[meckeard]

All,

I appreciate the input.

CajunCenturion -- You code was almost perfect. A small change and it works perfectly.

Thanks again everyone.

Mark

 

[CajunCenturion]

As a follow up - TomThumbKP is correct, the Replace would have to be inside of a while loop.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[daverx]

I was having a similar problem using Access 97. I wrote my own code for this function.

Public Function replace(theString As String, theOldValue As String, theNewValue As String)
replace = theString
If (InStr(1, theString, theOldValue, vbTextCompare) <> 0) Then
Dim theChar As Long
Dim newString As String
Do Until InStr(1, theString, theOldValue, vbTextCompare) = 0
newString = Left(theString, (InStr(1, theString, theOldValue, vbTextCompare) - 1)) & theNewValue
theString = Right(theString, Len(theString) - (InStr(1, theString, theOldValue, vbTextCompare) + Len(theOldValue) - 1))
Loop
newString = newString & theString
replace = newString
End If
End Function

 

[daverx]

Slight Change to the Code Above.

Public Function replace(theString As String, theOldValue As String, theNewValue As String)
replace = theString
If (InStr(1, theString, theOldValue, vbTextCompare) <> 0) Then
Dim theChar As Long
Dim newString As String
Do Until InStr(1, theString, theOldValue, vbTextCompare) = 0
newString = newString & Left(theString, (InStr(1, theString, theOldValue, vbTextCompare) - 1)) & theNewValue
theString = Right(theString, Len(theString) - (InStr(1, theString, theOldValue, vbTextCompare) + Len(theOldValue) - 1))
Loop
newString = newString & theString
replace = newString
End If
End Function