Text to columns with date format does not work :(

[Tacrier]

Hi there,

I have a text file that i would like to convert into Excel. There is a
column in my file that contains a date but it is not recognized in Excel.

I tried to use the Text to Columns feature using the date format, however it
worked for about 1/3 of my data. Most of the column remains unchanged.

The text file date shows as (for example):

Apr20/07

Any suggestions on an alternate solution to have my date recognized in Excel?

Thanking you in advance,
Trina

 

[Rick Rothstein \(MVP - VB\)]

Assuming your "dates" use 2-digit day values (leading zero for single digit
day number) and your dates are all in the current millennium, put this
formula in an unused (helper) column in the same row as the first "date"
(adjust the A1 reference to the cell containing the first "date")...

=--SUBSTITUTE(LEFT(A1,3)&" "&RIGHT(A1,5),"/",", 20")

and copy it down to the row with the last "date" in it. Select all the dates
generated by the above formula and Edit/Copy (or Ctrl+C) them, click on the
first "date" cell and click Edit/PasteSpecial, select the Values option and
click OK. Erase the helper column.

Rick