Excel 365 won't save format

Question

I have a .csv report where I change a number field to a text field, then save it. This is required because the field must be three characters, ie: '90' must be '090', to import to another software. This worked fine with Excel 2010 and 2016. Now with 365, Excel won't save the number as a text file. Suggestions, please?

Thanks,

Mel

Answer 1

By "number field" and "text field" do you mean Formatting as number and text respectively? If so then what works for me is to change Formatting to Custom 000(amount of numbers equivalent to number of necessary digits) then Save and Close. To check if it works, open file in Notepad (right click file and select Edit). If you open it in Excel, it will try to convert it to numbers again. At least for me it does and I do have 365.

Answer 2

Hi Mel,

Thank you for querying in this forum. We do understand the inconvenience caused and apologize for it. And we will try our best to help you.

According to your description, it seems that you used Excel 365 to edit your CSV file, and you changed the number field to text field in this file, however, you found that it didn’t save it as text file when you opened it in Excel application again.

May I know if my understanding is right? If so, we have tested it on our side, and we went to Excel and create a new CSV file, then we typed 90 and changed it to Text format and changed to 090, after reopening it again, and we found that the format has been changed to General.

We have also tested it with the latest version of Excel, per my test result, latest Excel won't preserve the prefixed ZERO value and the TEXT column is always default back to General. Via additional research on the behavior, it is more like a "by design" when using Excel to directly open .CSV files, because it will change format according to the value you typed in the cell. We have also tested it on our side with Excel 2016 and we encountered the same behavior in Excel 365.

However, if we opened it in Notepad or WordPad, it works fine and format will be kept.

If the scenario above is not consistent with yours, could you provide more details about your situation? May I know if you lost format when you import file to another software.

Best Regards,

Sukie

Answer 3

Sukie:

You have the issue correct. Excel 2016 works perfectly but Excel 365 will not save the format change. Note that this field must be changed to a 3 character text field so that it can be imported by another software program, so I using Notepad or Wordpad wouldn't help. And although Excel read saved, I don't think the format was saved at all.

I just re-installed 2016 on a computer and my csv format change works fine.  I need to fix the issue though. Maybe I need to find an Excel alternative.

Mel

Answer 4

I have a number field and I need to change it to a text field. We than manually add a leading '0' to make the field three characters, if needed. From there, a third party software imports the csv info. Whether I try to re-open it or not, the format defaults to numeric.   Not seeing a '000' option.

Mel

Answer 5

I have a number field and I need to change it to a text field. We than manually add a leading '0' to make the field three characters, if needed. From there, a third party software imports the csv info. Whether I try to re-open it or not, the format defaults to numeric.   Not seeing a '000' option.

Mel

Hi Mel, 

does your 3rd party software report that csv is lacking leading zero after adding zeroes in Excel or do you see it in notepad/other text editor too?

What I meant by change Formatting to Custom "000" is that you have to manually type 3 zeroes in formatting as marked in picture bellow. Once formatting is changed to "000" and csv saved in Excel, all numbers with less than 3 characters should have leading zero. Although if file is opened in Excel again, they will be lost.

If all else fails, leading zeroes could be added using text editor such as Notepad or WordPad although it will be much more inconvenient and time consuming.

Jan S.