I created a CSV file with a twelve numeric value id (as in a Medicaid Id). The Excel sheet converts this 12 digit number into a 1E+11 format. Is there a way to prevent this?

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. Ben

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.

Flikkery As the IT department, can you format the spreadsheet however you want, push it to your users and instruct them to use it? That way they will use a standardized template and they won't have to do any of the formatting.

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. Flikkery

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....

Formatting CSV numeric values | Microsoft Community Hub

11 Replies

Bennadeau
Iron Contributor
Jun 19, 2020
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.

Ben
- Flikkery
  Copper Contributor
  Jun 19, 2020
  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.
  - Maverick494
    Copper Contributor
    Jun 19, 2020
    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.
    Flikkery

Forum Discussion

Formatting CSV numeric values

11 Replies

Resources