* in date format

I have exported a report into Excel from a suppliers website.

It now includes a * infront the date format which is not displayed. I cannot remove it, the data displays a date as normal but is seens as text in the cell.

If I use the "Edit Replace" function it removes all the data and I can't find any other way to remove it. I think the spreadsheet thinks is a wildcard.

Geoff,

Use B:B as a helper column. Where your date is in A1 in B1 try this.

=SUBSTITUTE(A1,"*",,1)*1

Multiplying it by 1 converts it from text to a formattable date. Drag down the list and then you can Copy, Paste Special values over the old data and delete the helper column.

Does that help?

Steve

Hi Steve,

Thanks for your help, unfortunately it didn't work in this instance as the format has the * in front of it, it doesn't actually appear in the cell.

I managed to work around.

Geoff