Handling imported dates, some with a trailing space

An external query of a web database returns names and dates, Col A & Col B on the attached work book. Some, but not all, of the dates have a trailing space. I want to convert the dates (which are in US format mm/dd/yyyy) to their Excel date-time code so that I can perform subsequent operations and format UK dd/mm/yyyy.
I've browsed several possible solutions e.g. (TRIM) and Find/Replace which appear to remove the space(s) but the date then stubbornly refuses to convert to Excel date-time code. I've also tried the Web query with the option "Match Destination Formatting" (general) without success.

Is there a solution which doesn't involve VBA?

Use Data | Text to columns. First select the column of dates. Use space as a delimiter and specify that the column is a date in MDY format. Copy to another column for your safety. If they look OK, copy them over the originals.


Regards, TMS

Jonnie,
I am not sure what the issue is that is causing this. In an attempt to examine the contents of the cells in question, I highlighted one and clicked on the F2 (edit) key and then the Enter key and immediately the number converted correctly. I tried this on several other cells and they all converted. Very manual exercise if you don't want to use VBA, but doable depending on how large your data table is. You may be better off with a quick macro to loop through the data if your data is very large.

Alan

text to columns will certainly fix it no need for an delimiter just select column use mdy click finish

You're welcome. Thanks for the rep.