a formula to show date only not letters or characters

[kmis]

Hi - I'm new to this, but wondering if there is a formula or macro to help with the following:

in Cell A1 to A5 I have:
24-dec-12
13-feb-14A
24-nov-89*
blank cell
19-may-12

What I want the formula to be able to do is display the following in cell B1 to B5:
24-dec-12
13-feb-14
24nov-89
blank cell
19-may-12

And in cells C1 to C5 it should show:
Blank cell
A
*
blank cell
blank cell

I have only shown 5 cells worth of data, however I have over 1000 cells I need to do. Thanks for trying to solve this.

[:) Sixthsense :)]

In B1 Cell

Please Login or Register  to view this content.

In C1 Cell

Please Login or Register  to view this content.

Drag both the formula's down...

[kmis]

Hi sixthsense,

thanks for the response however, it didn't not work.

I have attached a spread sheet showing what I want.

tab1 shows the original data
tab2 shows what I would like to see
tab3 shows what outcome your formula gave me

[FDibbins]

change Six's formula to...
=IF(ISNUMBER(A3),A3,LEFT(A3,9)*1)
then format all B to date. the 5-digit number is in fact a date -how excel sees a date, anyway...41267 days from 1/1/1900

[:) Sixthsense :)]

Please refer the attached file for solution.

[kmis]

Thanks FDibbins.

Your formula works for colB. However, where there is a blank cell it returns "#value" rather than leaving he cell blank.

As for colC the formula sixthsense gave, just shows a blank cell. I want it to show the * or A. Which ever it has omitted.

[FDibbins]

Im sure Six's file solves your questions

[kmis]

it does solve it. I posted the reply to you before I had seen sixthsense.

Thank you to both of you for this!

[FDibbins]

Happy to help and thanks for the feedback (Six did all the work though)