Excel Date Format not in correct format

[newbie4]

Hi,

I have a date in excel that have extra spaces, but it is in text format because excel doesn't recognize it as date.

It is currently in this format: May 10 2016

2 spaces after May

I have tried several formulas with no luck.
=IF(ISTEXT(A1),DATEVALUE(MID(A1,1,10)), INT(A1))

Any solutions that will convert the text into date format. I actually need it in XX/XX/XXXX format

-Newb

[Sam Capricci]

Could you provide more examples? Maybe a simple find 2 spaces and replace all with one space would work?

[newbie4]

I actually need it in XX/XX/XXXX format. I tried the space and then format. didn't work

May 2 2016
Apr 23 2016
May 10 2016

Thank you!
-Newb

[Sam Capricci]

ok, using your sample, it took me a couple find and replace all, find may, replace with 5, find apr replace with 4, find space and replace with /.
once it was done all the dates converted to dates.

[Phuocam]

Try this ...

=--TRIM(REPLACE(A1,FIND(" ",A1,6),1,", "))

[newbie4]

Trim function worked perfectly. I never used it, but will start using the trim coupled with the replace and find function. -Newb

[Phuocam]

You're welcome!