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
|
|||
|
|||
Excel auto formats cells with numbers - Can it be disabled?
I am having a problem within Excel, all versions.
A piece of software I use daily uses CSV files for inventory management for Books, CD's and DVD's. The problem happens when you enter in an ISBN or UPC that is all numbers and starts with a 0. Excel will see all numbers and automatically format the cell as a number, thus dropping the leading 0. I know you can put a dash, pipe character or whatever to stop this. I also know you can right click on cells and change formatting. But the problem with both of these are 1: CSV's do not save formatting, so the 0 will be dropped once the file is opened again. 2: The program I use will not allow a hyphen or Pipe character in the UPC or ISBN's. So, essentially, what I need is an option to have Excel to *Never* automatically format cells depending on their context. I have gone through and disabled *Every* auto formatting and such rules I could find in every corner of the options. I have also used the Import text wizard to stop it and it doesn't work. Am I right? Is there no way to stop Excel from formatting cells automatically? Thanks |
#2
|
|||
|
|||
Excel auto formats cells with numbers - Can it be disabled?
Hi Jeromey,
You could add a prefix to the code, then after the CSV has been brought into Excel go a global change on the column to remove the prefix. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Jeromey" wrote in message ... I am having a problem within Excel, all versions. A piece of software I use daily uses CSV files for inventory management for Books, CD's and DVD's. The problem happens when you enter in an ISBN or UPC that is all numbers and starts with a 0. Excel will see all numbers and automatically format the cell as a number, thus dropping the leading 0. I know you can put a dash, pipe character or whatever to stop this. I also know you can right click on cells and change formatting. But the problem with both of these are 1: CSV's do not save formatting, so the 0 will be dropped once the file is opened again. 2: The program I use will not allow a hyphen or Pipe character in the UPC or ISBN's. So, essentially, what I need is an option to have Excel to *Never* automatically format cells depending on their context. I have gone through and disabled *Every* auto formatting and such rules I could find in every corner of the options. I have also used the Import text wizard to stop it and it doesn't work. Am I right? Is there no way to stop Excel from formatting cells automatically? Thanks |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Stop excel from dropping the 0 in the beginning of a number? | Rosewood | Setting up and Configuration | 11 | February 12th, 2006 01:38 AM |
checking that cells have a value before the workbook will close | kcdonaldson | Worksheet Functions | 8 | December 5th, 2005 04:57 PM |
Linking Excel cells to a Word document | jada | Mailmerge | 3 | November 14th, 2005 08:17 PM |
blank cells with auto fill in excel | derecl | Worksheet Functions | 2 | November 2nd, 2005 09:11 AM |
How to auto format phone numbers in Outlook as you can do in Excel | Haishan | Contacts | 0 | March 15th, 2005 05:29 PM |