Text to columns with date format does not work :(
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 |
Text to columns with date format does not work :(
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 "Tacrier" . wrote in message ... 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 |
All times are GMT +1. The time now is 08:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com