I can't format 01/19/2004 to read January 19, 2004, please help!!

[Guest]

Dragged and dropped a series of dates and now I can't convert them to sort
them properly. What to do??

 

[Norman Jones]

Hi Paul,

I can't format 01/19/2004 to read January 19, 2004, please help!!
Dragged and dropped a series of dates and now I can't convert them to sort
them properly. What to do??

With the date cells selected:

Data | Text to Columns | Next | Next | Select the Date option and Select
the DMY option in the dropdown box | Finish

 

[Guest]

Hello Norman,

I followed your instructions to the letter several times without any
success. I've pasted the values as "values", without changing any format,
and I'm unable to reformat them at all. Would this have anything to do with
the "/"?

Thanks for your assistance, I think I'm getting closer....

 

[Norman Jones]

Hi Paul,

I copied 01/19/2004 from your posted line:

I can't format 01/19/2004 to read January 19, 2004, please help!!

into a number of cells in a column. I then :

1) Selected Text to Columns on the Data menu (with the cells selected!)
2) Checked the 'Fixed Width" option
3) Hit the 'Next' button
4) Hit the 'Next' button
5) Checked the 'Date' option
6) Selected 'MDY' in the dropdown
7) Hit the 'Finish' button

This converted the original text values to dates which I was then able to
format as desired.

 

[Guest]

Hello Norman,

Y'know, I think I've discovered the problem. You were absolutely right of
course, once I found out what else was going on.

It seems that the dates that I copied from an online source have a space
placed before and after the value (which I've been unable to eliminate
without going through each cell seperately and deleting it). For example,
"1/19/2004" is actually being pasted as " 1/19/2004 ". Once the space has
been removed from in front of the value (rear space doesn't seem to matter),
I'm able to follow your instructions and it works like a charm. Sorry for
doubting you!!!

By the way, if you happen to know, how can I get rid of these damn spaces
efficiently I'd be eternally grateful!

Thanks for your patience and persistence!

Paul

 

[Norman Jones]

Hi Paul,

By the way, if you happen to know, how can I get rid of these damn spaces
efficiently I'd be eternally grateful!

See David McRitchie's TrimAll sub at:

http://www.mvps.org/dmcritchie/excel/join.htm - trimall

Additionally, if you are not familiar with macros, see David McRitchie's
introduction notes at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm