Space in text

[Raj]

when I copy text from on sheet to anoth, the destination after the paste has
one space in front of the text. how do i remove the space from multiple cells
at the same time

 

[JoeSpareBedroom]

when I copy text from on sheet to anoth, the destination after the paste
has
one space in front of the text. how do i remove the space from multiple
cells
at the same time

If this is just a one-time cleanup you need to do, use the TRIM() function
in another column.

For instance, if you have this in A5, and the underline represents a space:

_This is some text.

You'd put this in another column:

TRIM(A5)

Then, use copy & paste special-value to copy the repaired text back into the
original column.

If this problem is a recurring one, you need to find out why, or perhaps
someone can suggest some VBA code that will scan the whole column and make
the necessary changes.

 

[Gord Dibben]

If a one-time operation see the other reply about using TRIM function by
formula.

If an on-going problem use this macro on the selection.

Sub TRIM_EXTRA_SPACES()
Dim Cell As Range
For Each Cell In Selection
If (Not IsEmpty(Cell)) And _
Not IsNumeric(Cell.Value) And _
InStr(Cell.Formula, "=") = 0 _
Then Cell.Value = Application.Trim(Cell.Value)
Next
End Sub


Gord Dibben MS Excel MVP