macro to rearrange numbers in cell?

[jim]

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/

 

[Don Guillett]

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

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

 

[bodhi2.71828]

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.

 

[jim]

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

 

[Don Guillett]

glad to help