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,

--
Farhad Hodjat


"Junderwood57" wrote:

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!

another way.

Select your column
Data\Text to colums|Fixed width|date|YMD
Finish

Mike

"Junderwood57" wrote:

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!

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)

Junderwood57 wrote:

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!

--

Dave Peterson

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

"Junderwood57" wrote in message
...
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!

DataText to ColumnsNextNextColumn Data FormatDateYMD and Finish.


Gord Dibben MS Excel MVP

On Tue, 14 Aug 2007 08:48:04 -0700, Junderwood57
wrote:

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!