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

"Raj" wrote in message
...
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.

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

On Sat, 10 May 2008 16:31:01 -0700, Raj wrote:

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