how to clear up mixed date/text column

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.

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


<[email protected]> escribió en el mensaje
news:[email protected]...
>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.
>

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)?

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.


<[email protected]> escribió en el mensaje
news:[email protected]...
> 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)?
>