Unwanted spaces

[Guest]

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)

 

[Guest]

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

 

[L. Howard Kittle]

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

 

[Sandy Mann]

Just another option:

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

--
HTH

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk