Paste Special?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm creating a very large spreadsheet and have a question, with hopefully a
simple solution. The dates that pulled into Exel are formatted as 20070529.
There are over 1500 lines with dates this way and all different dates. How
do I chnage these dates to format as a regular 05/07/2007? the format cell
function does not work and to manually change would take quite a while. I'm
not sure if there is a way to paste special? Anyone's help would be
appreciated.
Thanks!
 
Hi,

in a helper column ( empty column) enter this formula ( asumed your original
column is A)

=LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,4)

copy drag down the formula to your 1500 line
copy >paste especial >value your helper column to youe original column
delete helper column

Thanks,
 
If the dates are in a single column:

Select the column
Data|text to columns
fixed width (erase any lines that excel guessed)
Choose ymd as the format for the incoming data
finish up

format that column they way you like (mm/dd/yyyy)
 
Here's a macro solution. Assumes your dates are in column c. Change the
column designation as needed in the code. Assumes all the dates are in the
same format, 8 characters long. This overwrites the original data. After
running, format the column for dates.
James

Sub FixDates()
Dim Yr As String, Mo As String, Da As String, Str As String, k As Long
On Error Resume Next
For k = 2 To Cells(65536, "c").End(xlUp).Row
Yr = Left(Cells(k, "c"), 4)
Mo = Mid(Cells(k, "c"), 5, 2)
Da = Right(Cells(k, "c"), 2)
Str = Mo & "/" & Da & "/" & Yr
Cells(k, "c") = CDate(Str)
Next k
End Sub
 
Data>Text to Columns>Next>Next>Column Data Format>Date>YMD and Finish.


Gord Dibben MS Excel MVP
 
Back
Top