why i cannot change a certain date format

[Lisah]

i use jmp program and save file as .xls. when i open in excel, all data in
each cells shows up with apostrophes. I cannot remove the ' sign and cannot
change the date format
e.g 08/03/2007 01:30 AM to 2007/08/03 01:30.
I aslo encountered a box sig in between an empty space between characters.
How to remove them?
Anybody has any idea on how to solve these problems??

 

[Dave O]

Hi, Lisa-
This happens a lot: I use this routine to correct it. Copy this code
and paste it into your spreadsheet as a macro, then highlight a range
of cells that contains the dates you want to change and run the macro.

Dave O


Sub Selected_Range_Format()
Dim rCell As Range
Dim TrueVal As Variant
Application.Calculation = xlCalculationManual

For Each rCell In Selection.Cells
TrueVal = Trim(rCell.Value)
rCell.ClearContents
rCell.NumberFormat = "yyyy/mm/dd hh:mm"
rCell.Value = TrueVal
Next rCell
Application.Calculation = xlCalculationAutomatic
End Sub