Ensure Excel retains field value as text string, rather than converting to number

[glennlee]

Hey all,

Prob a stupid question. But is there a way to prevent Excel from converting a text string value in a CSV field, to a number?

I have a valid string value in a field "000000000", but as soon as I open the file in Excel, it converts it to "0", as it treats that field as a number.

Short of running the CSV import wizard each time to specify the column format, is there any other thing that can be done (to the CSV or to Excel), to stop this from happening?

Thanks in advance.

Glenn

[#2]

@glennlee 

It sounds like this is also a CSV file you are importing repeatedly. So what's the source? Can something be done at the starting end to make that file clearly a text file (e.g., instead of it reading as "000000000" make it "TXT000000000" and then just use a formula to strip off the "TXT"? Granted, it's still a step, but if it's a repetitive thing, you could write a macro or VBA that takes care of that.

The other possibility, if it's really only and always "000000000" --and I realize you may have just been using that as an illustration of a generic 9-character code--but if it's really just nine "0" characters, then it has no meaning anyway.

Then there's always a VBA routine that goes through the import wizard......

[For the record, I'm not someone who writes VBA routines, but I do know they can be helpful at times.]

[#3]

@mathetes - Thanks for the pointers. The file is coming from a piece of software that my org built. We can certainly do things to alter the outputs in the file, but was hoping that there was something we could do to either label certain data in the CSV a particular way so it was treated the same each time by Excel or get Excel to handle file fields a certain way, without building in VBA or going through CSV import wizard. In other words, hoping to avoid actually tampering with the file contents to force this.

[#4 · Accepted solution]

@glennlee You can connect to the CSV with Power Query and set-up the transformations once. Like indicating that this particular column should be seen as text. Next time when the CSV gets updated, refresh the query and the import will run automatically as is was set up the first time.

[#5]

@glennlee 

You could wait for the "control data conversions" feature which is still in Beta/Insider.

https://insider.office.com/en-us/blog/control-data-conversions-in-excel