| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell format with Data Import (date appearing as text)
Help!
I have a file which I download from an intranet application. For some reason one of the columns that comes in as a date field, is coming through in some cells OK and in others as a text format (this is easily identifiable depending on whether they are justifies to the left or right of the cell). All the dates are valid (not 30th Feb), and my regional setting are fine too. What I don't understand is that in all other columns the dates are fine, but for some reason this one column is a mix and match of date and text and is causing me problems (i want to import it into access). The only way to get around this is to manually click in each of the text format cells and hit enter and they change to date format and are correct, problem is i have over 10,000 lines like this! Any suggestions appreciated, Thanks, |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell format with Data Import (date appearing as text)
I'm guessing that there's more going on than you're aware of. Excel usually
exhibits that behavior when it's dealing with text. Do the problem values have leading or trailing spaces? Meanwhile, you might try this: Select the column of dates Then, from the Excel main menu: <data<text-to-columns Click [next] twice Column data format: Date Click [Finish] Does that help? *********** Regards, Ron XL2002, WinXP "Louise" wrote: Help! I have a file which I download from an intranet application. For some reason one of the columns that comes in as a date field, is coming through in some cells OK and in others as a text format (this is easily identifiable depending on whether they are justifies to the left or right of the cell). All the dates are valid (not 30th Feb), and my regional setting are fine too. What I don't understand is that in all other columns the dates are fine, but for some reason this one column is a mix and match of date and text and is causing me problems (i want to import it into access). The only way to get around this is to manually click in each of the text format cells and hit enter and they change to date format and are correct, problem is i have over 10,000 lines like this! Any suggestions appreciated, Thanks, |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell format with Data Import (date appearing as text)
Try this to see if it works:
highlight the column with the offending dates, click Data | Text-to-columns then click <Finish on the first panel. Hope this helps. Pete Louise wrote: Help! I have a file which I download from an intranet application. For some reason one of the columns that comes in as a date field, is coming through in some cells OK and in others as a text format (this is easily identifiable depending on whether they are justifies to the left or right of the cell). All the dates are valid (not 30th Feb), and my regional setting are fine too. What I don't understand is that in all other columns the dates are fine, but for some reason this one column is a mix and match of date and text and is causing me problems (i want to import it into access). The only way to get around this is to manually click in each of the text format cells and hit enter and they change to date format and are correct, problem is i have over 10,000 lines like this! Any suggestions appreciated, Thanks, |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell format with Data Import (date appearing as text)
Wow - Thank you - you have just saved me hours each month! It worked
perfectly!!! "Ron Coderre" wrote: I'm guessing that there's more going on than you're aware of. Excel usually exhibits that behavior when it's dealing with text. Do the problem values have leading or trailing spaces? Meanwhile, you might try this: Select the column of dates Then, from the Excel main menu: <data<text-to-columns Click [next] twice Column data format: Date Click [Finish] Does that help? *********** Regards, Ron XL2002, WinXP "Louise" wrote: Help! I have a file which I download from an intranet application. For some reason one of the columns that comes in as a date field, is coming through in some cells OK and in others as a text format (this is easily identifiable depending on whether they are justifies to the left or right of the cell). All the dates are valid (not 30th Feb), and my regional setting are fine too. What I don't understand is that in all other columns the dates are fine, but for some reason this one column is a mix and match of date and text and is causing me problems (i want to import it into access). The only way to get around this is to manually click in each of the text format cells and hit enter and they change to date format and are correct, problem is i have over 10,000 lines like this! Any suggestions appreciated, Thanks, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| text and date format in a cell | Excel Discussion (Misc queries) | |||
| Text and dates into one cell without loosing date format | Excel Discussion (Misc queries) | |||
| run a macro in a locked cell | Excel Discussion (Misc queries) | |||
| Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
| Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Linear Mode
