transform text dates into Excel dates

D

Daniel P

Hell

I currently have a spreadsheet with hundreds of entries wheremany of the date entries were done textually ("21 Nov. 2002"
But I need them to be date format Excel won't let me change the format/ won't recognize the text entries as a valid date entry. How can I convert these entries so the Excel will permit me to change the format and recognize the entries as valid dates?

Thank yo

Daniel
 
L

Loli

Try doing data - text to columns to separate the day, month and year. Then concatenate the values in order with a "/" in between so that it'll be a valid entry.
 
G

Gord Dibben

Daniel

Data>Text to Columns>Delimited>Next>Next. Column Data Format>Date>DMY>Finish.

No need to split and concatenate.

Gord Dibben Excel MVP
 
D

Dave Peterson

One more option (depending on how the month abbreviations are used).

I could just select that range/column and
edit|replace
what: . (period)
with what: (leave blank)
replace all.

xl2002 (usa version) converted them to dates--I did have to reformat to show the
way I like.
 
Top