If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Auto Date Conversion Not Requested
When opening a .csv file or pasting in a column of number with the format of "01-5308", Excel autoformats to "Jan-08"...then when converting the format of the column to text, Excel translates to the date range numeric of "1244748". Nifty feature for corrupting data accuracy.
How do I turn off such a nifty feature? |
#2
|
|||
|
|||
Auto Date Conversion Not Requested
Pre-format the column as Text.
Formatting to text after the data is already present usually doesn't work. An alternative when entering (keying in) this type of data is to precede the data with an apostrophe ('), which is not visible in the cell itself, but can be seen in the formula bar. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bad Date Mark" Bad Date wrote in message ... When opening a .csv file or pasting in a column of number with the format of "01-5308", Excel autoformats to "Jan-08"...then when converting the format of the column to text, Excel translates to the date range numeric of "1244748". Nifty feature for corrupting data accuracy. How do I turn off such a nifty feature? |
#3
|
|||
|
|||
Auto Date Conversion Not Requested
Thank you for the reply, however, it appears that when opening files and/or pasting data the "pre-formatting" to text gets reset to the nifty conversion feature.
To date, I have been pasting to a notepad, then to Excel with limited luck. "Ragdyer" wrote: Pre-format the column as Text. Formatting to text after the data is already present usually doesn't work. An alternative when entering (keying in) this type of data is to precede the data with an apostrophe ('), which is not visible in the cell itself, but can be seen in the formula bar. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bad Date Mark" Bad Date wrote in message ... When opening a .csv file or pasting in a column of number with the format of "01-5308", Excel autoformats to "Jan-08"...then when converting the format of the column to text, Excel translates to the date range numeric of "1244748". Nifty feature for corrupting data accuracy. How do I turn off such a nifty feature? |
#4
|
|||
|
|||
Auto Date Conversion Not Requested
Have you tried "PasteSpecial", then select "Text".
This should retain (protect) the *pre-formatted* integrity of the column. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Bad Date Mark" wrote in message ... Thank you for the reply, however, it appears that when opening files and/or pasting data the "pre-formatting" to text gets reset to the nifty conversion feature. To date, I have been pasting to a notepad, then to Excel with limited luck. "Ragdyer" wrote: Pre-format the column as Text. Formatting to text after the data is already present usually doesn't work. An alternative when entering (keying in) this type of data is to precede the data with an apostrophe ('), which is not visible in the cell itself, but can be seen in the formula bar. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bad Date Mark" Bad Date wrote in message ... When opening a .csv file or pasting in a column of number with the format of "01-5308", Excel autoformats to "Jan-08"...then when converting the format of the column to text, Excel translates to the date range numeric of "1244748". Nifty feature for corrupting data accuracy. How do I turn off such a nifty feature? |
#5
|
|||
|
|||
Auto Date Conversion Not Requested
Thank you for the reply. Unfortunately, when pasting special, the data now must be parsed into columns (adding four more steps).
In addition, when opening a .csv file with the data 01-5088 in a column - the auto conversion feature to a date occurs. How does one turn off such features? "RagDyeR" wrote: Have you tried "PasteSpecial", then select "Text". This should retain (protect) the *pre-formatted* integrity of the column. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Bad Date Mark" wrote in message ... Thank you for the reply, however, it appears that when opening files and/or pasting data the "pre-formatting" to text gets reset to the nifty conversion feature. To date, I have been pasting to a notepad, then to Excel with limited luck. "Ragdyer" wrote: Pre-format the column as Text. Formatting to text after the data is already present usually doesn't work. An alternative when entering (keying in) this type of data is to precede the data with an apostrophe ('), which is not visible in the cell itself, but can be seen in the formula bar. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bad Date Mark" Bad Date wrote in message ... When opening a .csv file or pasting in a column of number with the format of "01-5308", Excel autoformats to "Jan-08"...then when converting the format of the column to text, Excel translates to the date range numeric of "1244748". Nifty feature for corrupting data accuracy. How do I turn off such a nifty feature? |
#6
|
|||
|
|||
Auto Date Conversion Not Requested
After pre-formatting your XL columns to text, delete the .csv extension
before importing and/or opening this new file. XL will then not recognize it, and should open the Import Wizard, where you can then stipulate Text. AFAIK, there is no other way to prevent XL from converting these "numbers", except to make them "non-numbers", namely, TEXT. When you say that this forces you into four extra parsing steps, why not post here exactly what you need to accomplish, and perhaps there might be an easy and simple "TextToColumns" solution to your problem? -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bad Date Mark" wrote in message ... Thank you for the reply. Unfortunately, when pasting special, the data now must be parsed into columns (adding four more steps). In addition, when opening a .csv file with the data 01-5088 in a column - the auto conversion feature to a date occurs. How does one turn off such features? "RagDyeR" wrote: Have you tried "PasteSpecial", then select "Text". This should retain (protect) the *pre-formatted* integrity of the column. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Bad Date Mark" wrote in message ... Thank you for the reply, however, it appears that when opening files and/or pasting data the "pre-formatting" to text gets reset to the nifty conversion feature. To date, I have been pasting to a notepad, then to Excel with limited luck. "Ragdyer" wrote: Pre-format the column as Text. Formatting to text after the data is already present usually doesn't work. An alternative when entering (keying in) this type of data is to precede the data with an apostrophe ('), which is not visible in the cell itself, but can be seen in the formula bar. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bad Date Mark" Bad Date wrote in message ... When opening a .csv file or pasting in a column of number with the format of "01-5308", Excel autoformats to "Jan-08"...then when converting the format of the column to text, Excel translates to the date range numeric of "1244748". Nifty feature for corrupting data accuracy. How do I turn off such a nifty feature? |
#7
|
|||
|
|||
Auto Date Conversion Not Requested
I have an issue where my website host provides me with a batch database file
each day. In order to make the forms easier for the customer the dates asked in the forms are split into 3 list menus, with seperate day, month and year fields that all use the same name. The field values for all three are then concatentated into a single date field on the server with each field seperated by a comma. I receive a tab delimited text file each day with the contents. When I run the text import wizard I select all fields as text and according to my worksheet settings, all of the fields are text. However, when I run find & replace to remove the commas, Excell automatically concerts the results into date format. This is useless for me as I need to export the data to another program, as text, and print the data as text... WYSIWYG. How can this be either turned off, or accomplished in another way? Thank you in advance. "Ragdyer" wrote in message ... After pre-formatting your XL columns to text, delete the .csv extension before importing and/or opening this new file. XL will then not recognize it, and should open the Import Wizard, where you can then stipulate Text. AFAIK, there is no other way to prevent XL from converting these "numbers", except to make them "non-numbers", namely, TEXT. When you say that this forces you into four extra parsing steps, why not post here exactly what you need to accomplish, and perhaps there might be an easy and simple "TextToColumns" solution to your problem? -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bad Date Mark" wrote in message ... Thank you for the reply. Unfortunately, when pasting special, the data now must be parsed into columns (adding four more steps). In addition, when opening a .csv file with the data 01-5088 in a column - the auto conversion feature to a date occurs. How does one turn off such features? "RagDyeR" wrote: Have you tried "PasteSpecial", then select "Text". This should retain (protect) the *pre-formatted* integrity of the column. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Bad Date Mark" wrote in message ... Thank you for the reply, however, it appears that when opening files and/or pasting data the "pre-formatting" to text gets reset to the nifty conversion feature. To date, I have been pasting to a notepad, then to Excel with limited luck. "Ragdyer" wrote: Pre-format the column as Text. Formatting to text after the data is already present usually doesn't work. An alternative when entering (keying in) this type of data is to precede the data with an apostrophe ('), which is not visible in the cell itself, but can be seen in the formula bar. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bad Date Mark" Bad Date wrote in message ... When opening a .csv file or pasting in a column of number with the format of "01-5308", Excel autoformats to "Jan-08"...then when converting the format of the column to text, Excel translates to the date range numeric of "1244748". Nifty feature for corrupting data accuracy. How do I turn off such a nifty feature? |
#8
|
|||
|
|||
Auto Date Conversion Not Requested
I don't quite follow exactly what you're saying.
Do you mean that after the import into XL, you have this data in individual cells: 2,4,03 1,20,02 4,16,04 .... etc. AND, this data is all formatted as Text? You say you now want to replace the commas? With what? Do you want slashes (4/16/04)? Or what? If you do want to change 4,16,04 to 4/16/04 AND ... not have the 4/16/04 in "date format", you can try using a helper column to transform your data. With data starting in A1, enter this in B1: =SUBSTITUTE(A1,",","/") And drag down to copy as needed. Now, this should give you the "display" that you want. You now have to *remove* the conversion formula, leaving the displayed data behind. While the "new" column B is still selected, right click in the selection and choose "Copy". Right click again, and choose "PasteSpecial", and click on "Values", then OK. NOW, right click again, for the third time and choose "FormatCells". Click on "Text", then OK. And, if I guessed correctly as to what you're looking for, you should now have a column of "Text Formatted" data ready to export to your other program. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Ikkeboeken" wrote in message i.nl... I have an issue where my website host provides me with a batch database file each day. In order to make the forms easier for the customer the dates asked in the forms are split into 3 list menus, with seperate day, month and year fields that all use the same name. The field values for all three are then concatentated into a single date field on the server with each field seperated by a comma. I receive a tab delimited text file each day with the contents. When I run the text import wizard I select all fields as text and according to my worksheet settings, all of the fields are text. However, when I run find & replace to remove the commas, Excell automatically concerts the results into date format. This is useless for me as I need to export the data to another program, as text, and print the data as text... WYSIWYG. How can this be either turned off, or accomplished in another way? Thank you in advance. "Ragdyer" wrote in message ... After pre-formatting your XL columns to text, delete the .csv extension before importing and/or opening this new file. XL will then not recognize it, and should open the Import Wizard, where you can then stipulate Text. AFAIK, there is no other way to prevent XL from converting these "numbers", except to make them "non-numbers", namely, TEXT. When you say that this forces you into four extra parsing steps, why not post here exactly what you need to accomplish, and perhaps there might be an easy and simple "TextToColumns" solution to your problem? -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bad Date Mark" wrote in message ... Thank you for the reply. Unfortunately, when pasting special, the data now must be parsed into columns (adding four more steps). In addition, when opening a .csv file with the data 01-5088 in a column - the auto conversion feature to a date occurs. How does one turn off such features? "RagDyeR" wrote: Have you tried "PasteSpecial", then select "Text". This should retain (protect) the *pre-formatted* integrity of the column. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Bad Date Mark" wrote in message ... Thank you for the reply, however, it appears that when opening files and/or pasting data the "pre-formatting" to text gets reset to the nifty conversion feature. To date, I have been pasting to a notepad, then to Excel with limited luck. "Ragdyer" wrote: Pre-format the column as Text. Formatting to text after the data is already present usually doesn't work. An alternative when entering (keying in) this type of data is to precede the data with an apostrophe ('), which is not visible in the cell itself, but can be seen in the formula bar. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bad Date Mark" Bad Date wrote in message ... When opening a .csv file or pasting in a column of number with the format of "01-5308", Excel autoformats to "Jan-08"...then when converting the format of the column to text, Excel translates to the date range numeric of "1244748". Nifty feature for corrupting data accuracy. How do I turn off such a nifty feature? |
Thread Tools | |
Display Modes | |
|
|