Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get date/time to wrap (format - wrap text doesn't work)? | Excel Discussion (Misc queries) | |||
Text to Columns doesn't work | Excel Discussion (Misc queries) | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
the date format is not working ,sort by date doesn't work. | Excel Discussion (Misc queries) | |||
My date format will not work. | Excel Discussion (Misc queries) |