Excel Help

[David@USCA]

Like most debt collection companies, the company I work for uses Excel quite a bit when dealing with client portfolios. I think anyone that uses Collections MAX probably uses the program.

My issue is this. I had a recent spreadsheet from one of our clients which was the standard list of debtors and their information. One field I was having issues with was the bank account number field, which is full of various length numeric number strings. In formatting the spreadsheet within Excel (2003) I noticed the bank account field had several cells that were displaying the account numbers in a scientific notation, for example 1.234E+12, when the actual number was something like 12340000000. I would say that I'm a pretty experienced Excel user, I just don't use the advance features like macros and formulas very often. I have tried my best to format these cells so that they come up without any formulas and display all the numbers, but I have not been successful. Every time I save the spreadsheet after fixing it, when I open it back up the same issue appears.

Are there any expert Excel users out there that might know a trick to get rid of this problem? I'd be very appreciative of any help, as I know I will deal with this issue a lot. Oh and by the way, it ultimately has to be in CSV format for Collections MAX of course.

[admin]

this problem always happens when you open up a csv file in excel which you should never do.  excel will destroy your formatting and cause these problems.  you need to get the original excel file from the client and then convert to csv.

[David@USCA]

Convert it to CSV using Excel, right? That's what I have done in the past, but when I import that CSV file into CMAX I get the 1.234E+12 numbers in the bank account number field (a custom field).

[admin]

understood.  however that csv file was most likely opened in excel and saved as an excel workbook at some point which causes those errors.  csv files should only be opened using notepad so it preserves all data as text instead of converting it into scientific notation  like excel does when it opens a csv file.

[David@USCA]

I see....hmmm. I'm gonna have to come up with a solution to this. The file comes over as XLS.  I don't know how I am going to avoid it being "contaminated" by Excel. So there is no way that you know of to turn off the automatic use of scientific notation in Excel?

[admin]

the file will only be contaminated if the file was once a different format (csv) and opened in excel.  the client should format the column as text when they send it to you so  the formatting doesn't change.