Remove spaces from a string variable

Does anyone know if there is a function to remove or replace spaces within a string variable in VBA. I want something similar to the TRIM function that also removes spaces from middle of the string as well as the ends.

Thanks

Use the Replace method of Strings:

* To remove all spaces:
Replace(YourStringWithSpaces, " ", "")

* To replace spaces with underscores:
Replace(YourStringWithSpaces, " ", "_")

Hope this helps...

Originally Posted by blatham

Does anyone know if there is a function to remove or replace spaces within a string variable in VBA. I want something similar to the TRIM function that also removes spaces from middle of the string as well as the ends.

Thanks

Thanks but when I try this it doesn't recognise the Sub or Function. Does it need a reference to a particular library to be set for it to work?

Hmmm... wouldn't really know why it can't recognize... Replace is supposed to be a "standard" method. It's a member of the Strings Module. Try using dot-notation:

Strings.Replace("dunno why...", "dunno", "do know")

sorry, don't have much idea...

Originally Posted by blatham

Thanks but when I try this it doesn't recognise the Sub or Function. Does it need a reference to a particular library to be set for it to work?

Nah, perhaps it was introduced in a later version. Suppose 97 is a bit old now! Thanks anyway.

Hi blatham

Replace and some other string functions are not available in XL97

Use Substitute in XL97, and the slightly faster Replace for later versions.
The conditional #If VBA6 will prevent a compile error in XL97's VBA5.

Sub test()

#If VBA6 Then
myStr = Replace("dunno why...", "dunno", "do know")
#Else ' it's XL97
myStr = Application.Substitute("dunno why...", "dunno", "do know")
#End If

End Sub

Regards,
Peter T

"blatham" <[email protected]> wrote in
message news:[email protected]...
>
> Nah, perhaps it was introduced in a later version. Suppose 97 is a bit
> old now! Thanks anyway.
>
>
> --
> blatham
> ------------------------------------------------------------------------
> blatham's Profile:
http://www.excelforum.com/member.php...o&userid=19441
> View this thread: http://www.excelforum.com/showthread...hreadid=398507
>