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


  • Re: Remove all spaces from String

    In xl97 you might nned to do something like:

    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


    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

    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?


Participate now!

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