macro to rearrange numbers in cell?
 

We have general office workers in our school district who use an
antiquated student information system to download roster information in
text format for import into Excel. A user recently asked me if there is
a way to globally change the outputted date format 20011028 to 10282001
after it has been imported. I'm thinking a macro would do it. Any ideas
would be greatly appreciated.

Thanks,

-Jim Epler
Grossmont Union High School District
El Cajon, CA
http://edtech.guhsd.net/

macro to rearrange numbers in cell?
 

a macro
for each c in selection
c.value=right(c,4)&left(c,4)
next

a formula
=right(a2,4)&left(a2,4)

--
Don Guillett
SalesAid Software

"jim" wrote in message
oups.com...
We have general office workers in our school district who use an
antiquated student information system to download roster information in
text format for import into Excel. A user recently asked me if there is
a way to globally change the outputted date format 20011028 to 10282001
after it has been imported. I'm thinking a macro would do it. Any ideas
would be greatly appreciated.

Thanks,

-Jim Epler
Grossmont Union High School District
El Cajon, CA
http://edtech.guhsd.net/

macro to rearrange numbers in cell?
 

Or you could do:

=DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2))

(assuming date is in cell B2)

Then you could use whatever format you want for the date via menu item
Format | Cells.

macro to rearrange numbers in cell?
 

Thank you both. I ended up using the macro as a subroutine named
"CleanUp" and then reformatted the cells as a date 03/14/01.

-Jim


wrote:
Or you could do:

=DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2))

(assuming date is in cell B2)

Then you could use whatever format you want for the date via menu item
Format | Cells.

macro to rearrange numbers in cell?
 

glad to help

--
Don Guillett
SalesAid Software

"jim" wrote in message
ups.com...
Thank you both. I ended up using the macro as a subroutine named
"CleanUp" and then reformatted the cells as a date 03/14/01.

-Jim


wrote:
Or you could do:

=DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2))

(assuming date is in cell B2)

Then you could use whatever format you want for the date via menu item
Format | Cells.