Home |
Search |
Today's Posts |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting Text to Date using "Text to Columns"
Hi Stephen,
Thank you for responding. Am using Excel 2003. I have tried using formula such as Len() as well, its really 5 characters. As I even tried to use IF() to check if length is 5, append a 0 in front. However, with formulas in cells, the Text to Columns fucntion don't seem to work. The Columns/Date(DMY) keeps displaying 10208 as 10th Feb. Whereby it actually is 1st Feb. Result Original 16-Jan-08 160108 10-Feb-08 10208 26-Mar-08 260308 24-Jun-08 240608 Am running out of ideas here.... -- Kahoko Tsuki. "Stephen" wrote: "kahoko" wrote in message ... Hi all, I have a few columns of date in excel which is imported from external data source. It is shown as text in Excel. Some sample of the data are like - 240708 (which is 24th July 2008) - 10208 (which is 1 Feb 2008) However, when I use the Text to Columns/Date(DMY) function to convert them date, the following results is given - 240708 -- 24/07/08 (correct) - 10208 -- 2/10/08(INCORRECT) I tried with Text to Columns/Date(MDY), then - 240708 -- 240708 (nothing happen) - 10208 -- 1/02/08(correct) Is there any method in Excel I can use to convert this text to date? They come in a mix of 5 and 6 digits. Please help... Thank you. -- Kahoko Tsuki. I can't reproduce your results (using Excel 2000). For me, Text to Columns/Date(DMY) converts both correctly. Are you sure there are no invisible characters along with the 10208 data? Try a formula such as =LEN(A1) to check that the length of the string really is 5. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting a text form of "=Sheet2!A1" into a reference formula | Excel Discussion (Misc queries) | |||
Formating numbers &"Text" to appear as currency &"Text" in formula | Excel Discussion (Misc queries) | |||
Excel should support a proper inverse to "Text to columns" | Excel Discussion (Misc queries) | |||
Splitting text, like "Text to columns", but as a formula | Excel Discussion (Misc queries) | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) |