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

Thank

--
blatha
-----------------------------------------------------------------------
blatham's Profile: http://www.excelforum.com/member.php...fo&userid=1944
View this thread: http://www.excelforum.com/showthread.php?threadid=39850

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 t
work

--
blatha
-----------------------------------------------------------------------
blatham's Profile: http://www.excelforum.com/member.php...fo&userid=1944
View this thread: http://www.excelforum.com/showthread.php?threadid=39850

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

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

sorry, don't have much idea... :(

blatham Wrote:
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 t
work

--
T-®e
-----------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...fo&userid=2657
View this thread: http://www.excelforum.com/showthread.php?threadid=39850

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

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" wrote in
message ...

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