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


    Hi,


    try:


    Code
    strString = Replace(strString, " ", "")


    (where strString is your variabel of course)


    Gollem

    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

    Re: Remove all spaces from String


    thanks tinyjack thats wourks great, forgot that lowly 97 may not have the function replace



    Cheers
    Dunnes

Participate now!

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

Register Yourself Login