I copy data from a different program and paste it into a spreadsheet and some
of the cells have unused spaces behind the last character. When I link that
data into the cell I want to post it into, it does not center with those 8 or
10 spaces. How do I format my destination cell so it will automatically
remove those spaces? I.E data now = (3/15/07 10:24:30 )
and I want it like (3/15/07 10:24:30)
--
Loren

I had to create a macro to do just what you are looking for. I would run the
macro before I did my validation or moving, change as you need to:

Sub rmvSpace()
' use on LDA report to remove trailing spaces

Dim strUntrimmed As String, strTrimmed As String
Range("A2").Select

Do
If IsEmpty(ActiveCell) Then
Exit Sub
End If
strUntrimmed = ActiveCell.Text
strTrimmed = RTrim(strUntrimmed)
ActiveCell = strTrimmed
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell) = True

End Sub

"Loren" wrote:

I copy data from a different program and paste it into a spreadsheet and some
of the cells have unused spaces behind the last character. When I link that
data into the cell I want to post it into, it does not center with those 8 or
10 spaces. How do I format my destination cell so it will automatically
remove those spaces? I.E data now = (3/15/07 10:24:30 )
and I want it like (3/15/07 10:24:30)
--
Loren

Hi Loren,

If the data is always in that format, date, two spaces, hour, minutes,
seconds...

=LEFT(A1,18)&RIGHT(A1,1)

Then copy and paste special values to remove the formulas.

HTH
Regards,
Howard

"Loren" wrote in message
...
I copy data from a different program and paste it into a spreadsheet and
some
of the cells have unused spaces behind the last character. When I link
that
data into the cell I want to post it into, it does not center with those 8
or
10 spaces. How do I format my destination cell so it will automatically
remove those spaces? I.E data now = (3/15/07 10:24:30 )
and I want it like (3/15/07 10:24:30)
--
Loren

Just another option:

=TRIM(LEFT(A1,LEN(A1)-1)&")")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"L. Howard Kittle" wrote in message
. ..
Hi Loren,

If the data is always in that format, date, two spaces, hour, minutes,
seconds...

=LEFT(A1,18)&RIGHT(A1,1)

Then copy and paste special values to remove the formulas.

HTH
Regards,
Howard

"Loren" wrote in message
...
I copy data from a different program and paste it into a spreadsheet and
some
of the cells have unused spaces behind the last character. When I link
that
data into the cell I want to post it into, it does not center with those
8 or
10 spaces. How do I format my destination cell so it will automatically
remove those spaces? I.E data now = (3/15/07
)
and I want it like (3/15/07 10:24:30)
--
Loren