delete space in end of words

[reza_doang]

hi all,

how to delete space in the end of words, sample:
1. apple
2. apple

in no. 2 there is space after apple, how to delete using vba, since there is many space in 1 column.

hope you understand....

thank you

 

[FishingIsMoreFun]

LTrim(string)

 

[Jon von der Heyden]

Please try on a copy of your data first...

With Intersect(ActiveSheet.UsedRange,Columns("A"))
    .Value = Evaluate("if(row(),if(len(" & .Address & "),trim(" & .Address & "),""""))")
End With

 

[reza_doang]

Please try on a copy of your data first...

With Intersect(ActiveSheet.UsedRange,Columns("A"))
    .Value = Evaluate("if(row(),if(len(" & .Address & "),trim(" & .Address & "),""""))")
End With

HI Jon..
thanks for your response, but can you be more specific, i don't know about programming....how to run it? where should i put this macro?

thank you

 

[FishingIsMoreFun]

Sorry I made a mistake.

RTrim(string)

I am bit lost, Rtrim removes trailing spaces, is this not what you meant??

 

[reza_doang]

Sorry I made a mistake.

RTrim(string)

I am bit lost, Rtrim removes trailing spaces, is this not what you meant??

hi,
so sorry, but can you be teach me how to use this formula? i have data in colum F.

thanks

 

[Jon von der Heyden]

HI Jon..
thanks for your response, but can you be more specific, i don't know about programming....how to run it? where should i put this macro?

thank you

Right-click your sheet tab and click 'View Code'. The VBE should open. Next go insert > new module. Paste this code into the code pane that opens:

Sub TrimText()
    With Intersect(ActiveSheet.UsedRange, Columns("A"))
        .Value = Evaluate("if(row(),if(len(" & .Address & "),trim(" & .Address & "),""""))")
    End With
End Sub

Change Columns("A") to reference the column that contains the text with trailing spaces.

Hit Alt+Q to close the VBE

Now, make sure that the sheet with the trailing spaces is active.
Press Alt+F8 and run the macro called 'TrimText'.

Please remember to take a copy of your sheet first.

 

[Rick Rothstein]

With Intersect(ActiveSheet.UsedRange,Columns("A"))
    .Value = Evaluate("if(row(),if(len(" & .Address & "),trim(" & .Address & "),""""))")

Why the "if(row(),...) part? This seems to work just as well...

.Value = Evaluate("if(len(" & .Address & "),trim(" & .Address & "),"""")")

 

[Jon von der Heyden]

Why the "if(row(),...) part?

Habit I suppose, from doing formulae such as:

vlookup(a2:a10,sheet2!a:b,2,0)

The fingers do the typing, brain is engaged on something else.

 

[reza_doang]

Right-click your sheet tab and click 'View Code'. The VBE should open. Next go insert > new module. Paste this code into the code pane that opens:

Sub TrimText()
    With Intersect(ActiveSheet.UsedRange, Columns("A"))
        .Value = Evaluate("if(row(),if(len(" & .Address & "),trim(" & .Address & "),""""))")
    End With
End Sub

Change Columns("A") to reference the column that contains the text with trailing spaces.

Hit Alt+Q to close the VBE

Now, make sure that the sheet with the trailing spaces is active.
Press Alt+F8 and run the macro called 'TrimText'.

Please remember to take a copy of your sheet first.

thanks jon, works well