Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.setup
Jeromey
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.setup
David McRitchie
 
Posts: n/a
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to format cells to recognize numbers like 1.1.1? GStrawley Excel Discussion (Misc queries) 2 January 27th 06 07:54 PM
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? C-Man23 Excel Worksheet Functions 3 January 19th 06 10:52 AM
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? C-Man23 Excel Worksheet Functions 1 January 9th 06 02:23 PM
copy & paste spreadsheet cells from excel to outlook to excel mismarple Excel Discussion (Misc queries) 1 September 20th 05 11:16 PM
Does Excel support Auto fill on filtered data? Maria Excel Discussion (Misc queries) 1 August 26th 05 01:45 PM


All times are GMT +1. The time now is 10:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"