Change Excel Exponent Default Setting

[Scull]

I work for a company that is implementing a new chart of accounts to
manage our business. The new account length will be 12 characters in
length(e.g. 611500100001). Unfortunately, when this is entered in
Excel - Excel automatically changes the value to an exponent
(6.115E+11).
Now I know I can change the format to display the number correctly, but
actually want to either change the default exponent length setting or
worse case: remove the exponent feature altogether. One of the other
reasons that this will become a big problem is the fact that in my
company we utilize CSV (comma separated value) files extensively and we
modify it using Excel. Everytime a CSV file is opened all of the
accounts are changed to exponents.
Does anyone know of anyway to change excel's default setting for
switching a number to an exponent? If not, I'd even entertain the
thought of removing the exponent entirely from Excel as it will become
that big of a problem.
Thanks in advance for any feedback!

Rich S

 

[Gary Smith]

Make your account numbers text, and they'll stay just as you entered them.
Account numbers aren't really numbers anyway. They're identifiers that
happen to be composed of numerals.

 

[Scull]

Gary,
Thanks for the reply! That works fine for my XLS files but
unfortunately it does not work for my CSV files. Excel automatically
opens CSV files and makes the assumption that the account ID is a
number and changes it to an exponent. I've tried saving the CSV files
using various text identifiers which works well unless it's re-opened
with Excel. At my company, the CSV format has been the best medium
with which many users, inclusing approximately 50 non-technical users,
utilize to import data into various applications. In addition, I do
not think we have a suitable alternative file format with which we
could use to both modify then import data into our applications.
Any other feedback would be greatly appreciated!

Thanks!

 

[RagDyeR]

Try changing the file extension of your import file from .csv to .txt.
This should open the "Import Wizard", where you can stipulate a "Text"
format.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Gary,
Thanks for the reply! That works fine for my XLS files but
unfortunately it does not work for my CSV files. Excel automatically
opens CSV files and makes the assumption that the account ID is a
number and changes it to an exponent. I've tried saving the CSV files
using various text identifiers which works well unless it's re-opened
with Excel. At my company, the CSV format has been the best medium
with which many users, inclusing approximately 50 non-technical users,
utilize to import data into various applications. In addition, I do
not think we have a suitable alternative file format with which we
could use to both modify then import data into our applications.
Any other feedback would be greatly appreciated!

Thanks!

 

[Scull]

Thanks for the reply of changing the file extension, but I need
something even easier than that. The reason: I know our non-technical
users will be lost if/when the "Import Wizard" kicks in and it will
lead to more questions than answers.

 

[Dave Peterson]

If the CSV files you work with are always the same layout, maybe you could:

1. Change the name of the file to .txt
2. Start a new workbook
3. Record a macro when you open it and specify each field
4. Add headers/print layout/filters/subtotals, whatever else you can think of.
5. Add a button from the Forms toolbar to the first worksheet in that workbook.
6. Distribute that workbook with the macro to the users.

Save all your data files as .txt and tell them to open the
"WorkbookToImport.xls", click the giant button and select their filename.

With all the extra stuff you do in your macro (formatting, filters, freezepanes,
too), maybe the users will see that as an even better way than double clicking
on the .csv files.

If you need help with your recorded macro, post back.

 

[Scull]

Dave-
Thanks very much for your suggestion. It's certainly a reasonable
solution that I believe could be implemented without much difficulty.
In fact, I am actually pretty experienced with writing VBA macros so I
think I could probably do it myself, but I most certainly appreciate
the offer to help with the macro and if I choose to go with your
solution and run into problems - I'll be sure to post back.
I'd still prefer a solution that would change the default setting of
Excel, but don't know if that's even possible. In my business, there's
really no need for exponents at any point.
Well, thanks again for yours and everyone else's feedback!

 

[Dana DeLouis]

Without changing the file name extension, you could use Data | Import
External Data | Import Data

However, this will bring up the Import Wizard which I see you do not want to
do.
Perhaps make a template that has a macro that does this data importing for
you.

HTH