File with a text cfield onverts to scientific notation

[Amileaux]

I have a file that comes in as .csv (and now .xls) with a job number field. We have changed the program so we wrap the job number in double quotes ("jobnumber"). We did this to try a stop excel from converting some of these numbers (8 digits in length) to Scientific Notation. However, it did not work. We can clearly see the double quotes before we import the file - and excel still converts some of them. The only way I've gotten this to work is to change the .csv to .xls and then that forces the text import wizard to pop - then I'm able to make to force that field to text. My question is this (finally) - Why didn't wrapping the job number in double quotes work? Thank you. Marie

 

[TommyGun]

Excel still trys to convert this into a number, and if it can, then will put it as a number in your worksheet. Can you append a single quotation to the beginning of your job number (ie. "'jobnumber")? When Excel reads this it will read it as text and thus leave it be.

 

[Joe4]

Why can't just just reformat it the way you need it once it is in Excel? Are you trying to preserve leading zeroes?

 

[Amileaux]

Thanks for the tip on the single quote. Let me make sure I understand - all that is needed is to load a single quote in front of the job field when it is pulled? Or single quotes around the job number field. ie. 'jobnumber vs 'jobnumber'.

I tried to convert the field to General and the jobs with scientific notation became very large - converting after importing did not seem to work.

Thank you. Marie

 

[TommyGun]

A single quote in front of a value will force Excel to place it as text.

'jobnumber