Space in text
 

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

Space in text
 

"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.

Space in text
 

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