Changing date format in Excel cell 1

[stinkybee]

I have an excel worksheet that contains dates in the following format:

1st Jan 2010

I need to change this to the following format:

01/01/2010

Is there an easy way to do this?

Thanks

 

[Gruuuu]

Easiest way I can see is to Find/Replace the "1st", "2nd", "3rd" etc with 1,2,3,...
Then format the cell number type how you wish. At most it will take 10 find/replaces. But don't forget the "0th" (this takes care of 10th, 20th, 30th)

 

[SkipVought]

and faq68-5827

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Easiest way I can see is to Find/Replace the "1st", "2nd", "3rd" etc with 1,2,3,...

Just replace the TEXT part with NOTHING. Don't worry about any numbers.

Replace st with NOTHING and repeat for nd, rd, th

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[stinkybee]

Thanks for replies, I was hoping there would be a simple function as this is going to be a common occurance.

Maybe I should post on the VBA forum to try and get some code that could do this form me automatically.

 

[SkipVought]

Simply turn on your macro recorder and record doing the find & replace process to ALL 4 strings. then just run that macro every time!

SIMPLE!

Do post in forum707 if you have any VBA code questions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[stinkybee]

Excellent.

That is Simple.

Thanks very much.

 

[Gruuuu]

Good eye Skip! *

 

[SkipVought]

Just a bit of texual coersion! ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...and thanx, Gruuuu!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}