06-19-2020 08:29 AM
06-19-2020 08:41 AM
Hi @Flikkery ,
You can try formatting your cell as text.
Right click > Format Cells... > under "Number" tab, select "Text"
I don't think this will convert to the full 12 digits view if it has already been converted to "1E+11" but if you re-input the data it should stick.
06-19-2020 08:54 AM
@Bennadeau The end user has to format the column the way you suggested, formatting the cell with a "Custom" format, and replacing the "General" with twelve zeroes. This works, however, as an IT department furnishing the Excelsheet, we want to circumvent the user from formatting the column.
06-19-2020 09:36 AM
The thing is that what you are talking about isn't a CSV formatting issue, it is an excel display issue. Excel automatically makes those numbers into sci format. There is nothing you can change in excel to change that when you send a file. It is all about the User and they would have to change the format.
If you want to control the format and lock the workbook you would have to turn the CSV into an excel file and do all the number formatting and then lock the sheet so the end user doesn't have to do it.
Keep in mind all a csv file is, is a text file that excel recognizes commas as a way to make a column.
06-19-2020 09:41 AM
@Bennadeau We have a template to use for sending spread sheets over the internet via an attachment in an email. The spread sheet is all formatted, including several header records. However, now they want a CSV file, so we can't use the formatted excel sheet we send them. I was thinking maybe adding a =TEST(A1,"000000000000") string to the field....
06-19-2020 09:43 AM
@Maverick494 I figured. It's not the CSV data but Excel that is doing the conversion. We send an HTML formatted Excel Sheet with all the fields formatted the way they want it. However, since they want a CSV, I have stated that there is no way to prevent Excel from doing the SCI formatting...
06-19-2020 10:27 AM
Not sure if this will work for you but either you pre-pend the number with a single quote when you create the CSV which will format the cell as text or open that CSV using Notepad/Wordpad.
I get that this may not be the solution you're looking for but I hope it may lead you in the direction of your solution.
06-19-2020 11:16 AM
@Bennadeau Yes, by making the numeric value as an alphanumeric value with the addition of any none numeric value, like a single quote, within the value impedes Excel from the SCI formatting. This doesn't circumvent the need for the user to manipulate the data, since they will need to REPLACE the quote with nulls.