[petogaz]

hi guys !!

i'm wondering how to remove spaces from a string in VBA (access).

for instance :

"i'm a newbe in VBA programming".

the result should be :

"i'manewbeinVBAprogramming".

any idea guyz ?

i know there's a function call Alltrim() but this removes only spaces from right and left not from the middle of string.

thanks in advance for your help !

[petogaz]

ok !!! guyz i found my way !


here's the code :

Public Function RemoveSpaces(strInput As String)
' Removes all spaces from a string of text
Test:
   If InStr(strInput, " ") = 0 Then
      RemoveSpaces = strInput
   Else
      strInput = Left(strInput, InStr(strInput, " ") - 1) _
      & Right(strInput, Len(strInput) - InStr(strInput, " "))
      GoTo Test
   End If
End Function

[killians45]

just use mid. example is from something I had to write. But I was ignoring the " ' ", but instead of that name it spc = " ". So my code was like this:

Public Function serialNumdel(serialNum) As String

Dim x As Integer
Dim apo As String
Dim tempserialnum As String
apo = "'"

For x = 1 To Len(serialNum)
If Mid(serialNum, x, 1) = apo Then
tempserialnum = Mid(serialNum, 1, x - 1)
serialNum = tempserialnum & Mid(serialNum, x + 1, Len _
(serialNum) - 1)
x = x - 1
End If
Next

End Function


notice, serialnumber would be your text string, tempserialnumber would be just a temporary holder while we parsed through it and removed what was needed. Where you see this:

apo = "'"
change it to

spc = " "

of course, you'll need to change the declarations to

dim spc as string

instead of

dim apo as string

[petogaz]

thanks killians for your input. that also works fine .
thanks again.