how to clear up mixed date/text column

[lcheni]

I have a column of mixed date/text in an Excel Workbook. The workbook
is downloaded from SAP system.

The column contains data that look like dates , but up sorting, they
would appear at different places; the "istext" function would confirm
they are text or data.

03/21/2006 istext = true
03/21/2006 istext= false

How to fix problems like this?

I tried:
a) format->cell -> data -> date for the column etc.
b) copy/special paste -> text/value etc

but none worked for me.

Thanks for suggestions.

 

[Ing. Luis Fernando Ortiz M.]

Hi:

Try this:

Make a loop in a range and use VBA function ISDATE. this feature can
identify the text like dates and return true if is valid date.

If you use format only change the aspect and not then contents


Regars.

Luis F. Ortiz

 

[lcheni]

Thanks. I was using ISTEXT to identify those "NON Data" already.

But how to convert those being identified not "Valid Date" entries such
as "03/20/2006" to a valid date. I suspect those may have some some
hidden identifer to make them non-date entries. How to remove those
identifer(s)?

 

[Ing. Luis Fernando Ortiz M.]

Try this

Use functions like MONTH, YEAR and DAY to extract a parts of date and joint
it as you wish. later use CDATE to convert it in a valid date.

Regards.