Text to Numbers (Dates)

[jeffkirk]

I downloaded the attached file from a Brokerage website. I see that the dates are actually text. I have tried various conversion techniques (DATE(Cell), etc., but nothing seems to work.
Any ideas?

[etaf]

if you click on the column D
then use Data>TEXT to columns
and choose the date as M/D/Y
it should convert for you

you have a space at the front of the data
so i used
=VALUE(MID(D3,5,2)&"/"&MID(D3,2,2)&"/"&RIGHT(D3,4))
which converts to a date
note, i'm UK based hence the DD/MM/YYYY
format
then format as MM/DD/YYYY

[MrShorty]

The first thing I did was scroll through the text of one of the cells, and I noticed that each cell with a date string begins with a space character. I then entered =CODE(LEFT(cell,1)) and found that these are 160 non-breaking space characters (the one space character that TRIM() and CLEAN() cannot touch).

I use a SUBSTITUTE() function to get rid of the non-breaking space SUBSTITUTE(D4,CHAR(160),"").
Then use a DATEVALUE() function to convert the text string to a date serial number (which assumes my system defaults for date formatting). =DATEVALUE(SUBSTITUTE(...))

Will that work for you?

[DomSmith]

This works,
=DATE(RIGHT(E4,4),MID(E4,2,2),MID(E4,FIND("/",E4)+1,2))

But I think the problem is that there is a leading blank on all those dates, so =Trim(E4) will also do the trick

[jeffkirk]

OK, my ultimate goal is to sort the table by column D, oldest to newest, and sorry, the only one that works is DomSmith's, but it results in 4 numbers for each date (All others returned either #VALUE or #NUMBER). The result would not sort oldest to newest. However, I then took the results from DomSmith's formula and pasted special-values to another column, formatted it as a short date, and it then let me sort. Thanks to everyone.

[alansidman]

An alternative solution is to employ Power Query. Load the table to PQ and change the date format. Here is the Mcode. Then Close and Load to your Excel file.

Please Login or Register  to view this content.

[jeffkirk]

Thanks to all. Unfortunately, I have never worked with (nor ever heard of) "Power Query". Is the added language code? Thanks again.

[alansidman]

Look at the links in my signature for more information. It is part of Excel. Look on your Data Tab and it will be either Power Query or renamed Get and Transform.

[jeffkirk]

Alan, your suggestion re "Power Query worked. Tanks. Now, how can I get rid if the green fill?

[jeffkirk]

Never mind, I got it.

WOW, that Power Query is pretty slick.

I wonder why the EXCEL folks can't parcel out the algorithm that does the conversion and provide it as a separate function?

Thanks again to all who responded.