time date formatting issues

Maybe the imported data has spaces before and after the data and it is possible that the space between the date and time is not really a space but another character that looks like a space.

Select the space in the date/time string and copy it. Paste that into say M1. In N1 enter =CODE(M1) If the code isn't 32 then the space isn't a space.

If the date and time are entered in column A enter this in column B and copy down the length of your data.

Formula:

=TRIM(SUBSTITUTE(A1,CHAR(160)," "))*1

If the code in N1 isn't 160, use that number in the CHAR() function. Format the date and time the way that you want (dd/mm/yyyy hh:mm:ss)

The above will trim off any leading or trailing spaces and "fix" any strange spaces if you found a strange code for the space. A normal space between the date and time will be left alone.