delete trailing space in column

[J.W. Aldridge]

B2:B10000 consist of what supposed to be dates.
The only problem is, when I attempt to change format, it wont take.
Found out the problem is a trailing space at end.

I have tried several delete space codes, but to no avail.

Please.... any suggestions to delete trailing spaces?

2009-05-02 

 

[Kassie]

Have you tried =TRIM(ref)? In a column next to these dates, insert this
formula and copy down. Then copy the trimmed data and paste special as values
Then delete the incorrect column
--
HTH

Kassie

Replace xxx with hotmail

 

[Michael H.]

If familiar with VBA you could write a loop using the Trim function.

Drop a button on the sheet and paste in the following... (Note will
need to substitute "Sheet1" with the name of your sheet.)

Private Sub CommandButton1_Click()

Dim intRow as integer
Dim strCell as string

intRow = 2
intCol = "B"
strCell = "B" & Cstr(intRow)

Do While Len(strCell) > 0
Worksheets("Sheet1").Range(strCell) = Trim(Worksheets
("Sheet1").Range(strCell))
intRow = intRow + 1
strCell = intCol & Cstr(intRow)
Loop

End sub

Good luck!

 

[J.W. Aldridge]

was trying to go with vba, however vba code didn't work. may resort to
trim. thanx all

 

[J.W. Aldridge]

trim didnt work either

 

[The Code Cage Team]

Is this data imported from Access by any chance?

trim didnt work either

--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)

 

[Dave Peterson]

Select the column
Edit|replace
what: (space character)
with: (leave blank)
replace all

If you copied these dates from the web, you may have those HTML non-breaking
spaces in your data.

Change that what to:
What: alt-0160
Hit and hold the alt key while typing the 0160 on the numeric keypad.

==========
Another option would be to
select the column
Data|Text to columns
Fixed width
Draw a line after the last digit
Choose date for the first field (ymd???)
and skip for the second field
and finish up the wizard.

Then format the cells the way you like.

 

[J.W. Aldridge]

That html thingy was it...

Tried the alt-0160 and it worked.

Thanx!