Remove spaces within text string VBA

[johnrlalor]

I have a range of data (Cells G4: G52) which can be populated incorrectly with spaces, I am trying to implement some VBA code which will automatically review this range and remove spaces where appropriate.
Any thoughts?
Thanks

 

[James006]

Hi,

With VBA, the instruction Replace will do the job ...

But, beforehand, you need to determine the nature of your spaces ... char(32), char(160), etc ...

Hope this will help

 

[footoo]

To remove leading spaces, trailing spaces and intermediate double(or more) spaces :

[G4:G54] = [Trim(G4:G52)]

 

[Eli Panic]

The TRIM function in Excel removes all leading, trailing and multiple spaces. TRIM in VBA only removes leading and training spaces.

An easy way to overcome this in VBA is by using Application.Trim instead of just Trim. See below, taken from the VBA Immediate Window.

strInput = " President Barack Obama "
?Trim(strInput)
President Barack Obama
?Application.Trim(strInput)
President Barack Obama

 

[Eli Panic]

The TRIM function in Excel removes all leading, trailing and multiple spaces. TRIM in VBA only removes leading and training spaces.

An easy way to overcome this in VBA is by using Application.Trim instead of just Trim. See below, taken from the VBA Immediate Window.

strInput = " President Barack Obama "
?Trim(strInput)
President Barack Obama
?Application.Trim(strInput)
President Barack Obama

Edit to say the original string had loads of spaces within it, but the forum has trimmed these out. I hope this tip might prove useful for some.

 

[pgc01]

Hi

Remark:

Edit to say the original string had loads of spaces within it, but the forum has trimmed these out.

A simple way to avoid this is to use a code box

"    President     Barack     Obama       "
?Trim(strInput)
President     Barack     Obama
?Application.Trim(strInput)
President Barack Obama

 

[footoo]

The TRIM function in Excel removes all leading, trailing and multiple spaces. TRIM in VBA only removes leading and training spaces.

An easy way to overcome this in VBA is by using Application.Trim instead of just Trim. See below, taken from the VBA Immediate Window.

Not true.
Have you tried the code I posted? It removes leading, trailing and multiple intermediate spaces : [G4:G54] = [Trim(G4:G52)]

The code is merely a shorter version of : [G4:G54] = Evaluate("Trim(G4:G52)")

It could, of course be written as : [G4:G54] = Application.Trim([G4:G54])

 

[footoo]

By way of further explanation, the code I posted uses the worksheet TRIM function (the same as using Application.Trim)

 

[rlv01]

The TRIM function in Excel removes all leading, trailing and multiple spaces. TRIM in VBA only removes leading and training spaces.

That is how I understand it as well.

Sub TrimTest()
    Dim S As String

    S = "    President     Barack     Obama       "
    Debug.Print "'" & S & "' -->Length = " & Len(S)
    Debug.Print "'" & VBA.Trim(S) & "' -->Length = " & Len(VBA.Trim(S)) & " (VBA Trim)"
    Debug.Print "'" & Application.Trim(S) & "' -->Length = " & Len(Application.Trim(S)) & " (Application/Worksheet Trim)"
End Sub

'    President     Barack     Obama       ' -->Length = 41
'President     Barack     Obama' -->Length = 30 (VBA Trim)
'President Barack Obama' -->Length = 22 (Application/Worksheet Trim)

 

[footoo]

Originally Posted by Eli PanicThe TRIM function in Excel removes all leading, trailing and multiple spaces. TRIM in VBA only removes leading and training spaces.



That is how I understand it as well.

Quite right.
Please note that the code I posted does not use TRIM in VBA.
It uses the worksheet TRIM function.