Macro to remove extra spaces

Hi, I am getting data in which has multiple spaces in, I have tried using the following code which works in both VB5 and VB6, but does not appear to in VBA. Can anyone point me as how to amend this. Thanks


Public Function RemoveExtraSpaces(str As String) As String
' Removes Extra Spaces in between the words

str = Trim(str)

Dim L As Integer
Dim i As Integer
Dim S As String
Dim Prev_char As String * 1

S = ""

L = Len(str)
i = 1
Do
Prev_char = Mid(str, i, 1)
i = i + 1

S = S + Prev_char
If Prev_char = " " Then
Do While (i < L) And (Mid(str, i, 1) = " ")
i = i + 1
Loop
End If

Loop Until i > L
str = S
RemoveExtraSpaces = S

End Function

i do not see any reason why it should not work as well in vba as in vb6
but could be simplified a bit
tested in vba

vb Code:

mystr = "          asd       ddgfdf        eeoodldl      "
mystr = Trim(mystr)
s = ""
For i = 1 To Len(mystr) - 1
    If Mid(mystr, i, 1) = " " And Mid(mystr, i + 1, 1) = " " Then
    Else: s = s & Mid(mystr, i, 1)
    End If
Next
s = s & Mid(mystr, i, 1)
mystr = s

in vb6, office 2000 or later you could use
mystr = Trim(Replace(Replace(mystr, " ", " "), " ", " "))

you should use & to join strings
avoid using reserved words (Str) for variables as that may make a difference in vba

pete, people can't see double spaces in this:
mystr = Trim(Replace(Replace(mystr, " ", " "), " ", " "))

If Replace() is available then use this:

Code:

---

str1 = Trim(str1)
Do While Instr(str1, "  ")
  str1 = Replace(str1, "  ", " ")
Loop

---

If you are working with Excel VBA then you can use this:

Code:

---

str1 = Application.WorksheetFunction.Trim(str1)

---

You will see the different between VBA.Trim() function and WorksheetFunction.Trim().

Hi Guys, thanks for your suggestions. I think however there is an issue relating to finding and replacing spaces compared with characters and symbols.

With other data I get, I also have an issue with random extra 7 zeros occasionally in it. So 123400000005678 rather than the valid data of 12345678. I tried "temp = Replace(String(7, "0"), "", , 1)" and suprisingly that didn't work either, was going to try that for the spaces next.

I have also tried "temp1 = Replace((temp1), " ", " ") 'replace 4 empty spaces" for both the spaces and zeros with no success. Initially looking at some form of VBLookup command, but guess what? Wasn't playing either.

Going round in circles definately bugging me now.......

Anhn excuse my ignorance (self taught) but what exactly do you mean by str1 = Application.WorksheetFunction.Trim(str1), why would I want to do that if reading the data from a string rather than a worksheet?

Thanks

excels worksheet function trim removes all excess spaces from any string, prefix suffix and more than 1 between words
but is irrelevant for any other character

anhn's do loop will work to replace any number of any consequative characters, to a single character

note replace is not available in vb5 or vba in office 97

temp = Replace(String(7, "0"), "", , 1)
this would work if you got it right

vb Code:

temp = "123400000005678"
temp = Replace(temp, String(7, "0"), "")
MsgBox temp

i tested to make sure vba excel 2000

Quote:

---

Originally Posted by westconn1

excels worksheet function trim removes all excess spaces from any string, prefix suffix and more than 1 between words
but is irrelevant for any other character

I was under the belief that trim only removed spaces before and after words/sentances, so basically adding Ltrim and Rtrim together.

anhn's do loop will work to replace any number of any consequative characters, to a single character

I have tried anhn's loop trying to replace characters, I tried m & m, so 'mummy' to 'mumy', which has not appeared to work. I need to go back and look at my general data reading and processing and coding of it.

note replace is not available in vb5 or vba in office 97

I have office 2002 with vb6.5, replace works.

temp = Replace(String(7, "0"), "", , 1)
this would work if you got it right

vb Code:

temp = "123400000005678"
temp = Replace(temp, String(7, "0"), "")
MsgBox temp

i tested to make sure vba excel 2000

---

I will go try that, thanks.