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

JoeSpareBedroom
Sat May 10 18:40:54 CDT 2008

"Raj" <Raj@discussions.microsoft.com> wrote in message
news:413B19B2-ADC5-4C79-80C5-B8492B4D5C1A@microsoft.com...
> 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
Sat May 10 19:34:56 CDT 2008

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 <Raj@discussions.microsoft.com> 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