Dates copied from webpage not recognised by excel

Hi

I copied some columns of dates form a website into an excel workbook. Unfortunately excel does not recognise the text and therefore I can’t sort the columns from oldest to newest.
I have tried the operations suggested here http://office.microsoft.com/en-001/e...003056131.aspx as I though spaces might be the problem but with no success.
Can anyone help?
Thanks

You have the non-breaking space character (with a code of 160) at the start of your values in column A - this is common when you download things from a website. Put this formula in B1:

=DATEVALUE(SUBSTITUTE(A1,CHAR(160),""))

Format the cell as a date, and then copy down.

Hope this helps.

Pete

Hi,

I have an Idea.
Follow the instruction:-

1. Using Text to column, split the details into 3 columns (Date, Month, Year)
2. Remove the spl character in Date column (first column).
3. change the month (values) into numbers use this formula - =MONTH(DATEVALUE("01 " &C2&" 2004"))
4. Use the formula "=DATE(D2, E2, B2)" and then sort accordingly.

-Nanju

try this
=--MID(A1,2,LEN(A1)-1)

Format the cell as a date, and then copy down.

Cheers. That did the trick.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete