Remove all spaces from String

  • Hi,
    im just writing a bit of VBA code in excel and wondered how i could remove all spaces from a string. I dont believe trim or ltrim/rtrim would work as these still leave spaces. An example of the string would be "Leeds_Bradford" (underscore is a space)and i want the output to be "LeedsBradford"
    Any help would be great


    Cheers
    Dunnes

  • Re: Remove all spaces from String


    In xl97 you might nned to do something like:


    [vba]
    Dim strOut as String
    Dim lngLoop as Long


    For lngLoop = 1 to Len(strString)
    If Mid(strString, lngLoop, 1) <> " " Then
    StrOut = StrOut & Mid(strString, lngLoop, 1)
    End If
    Next
    [/vba]


    TJ


    ps This is based on Replace not being available to xl97 (if my memory is correct).
    If Instr is available you could do something based on that, with a loop.

  • Re: Remove all spaces from String


    Gollum,
    thanks for the reply this is the code i have tried...


    AreaName = Application.WorksheetFunction.Replace(AreaName, " ", "")


    but i get the error message " argument not optional" and the '.replace' keyword highlighted


    Any ideas?


    Cheers

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!