Stupid Scientific Category

[t2true]

This whole scientific non-sense that Excel defaults to for some numbers is quite annoying.

Background:
I have a list of phone extensions from a PBX. The list includes the port number to which each extension is associated. The following is an example of some of the ports: 01E0205, 01E0713, 01E0916, etc. (I can have up to 3800 ports that look like that.)
For every one of the ports that looks like that, Excel outputs it as 1E+205, 1E+713, 1E+916, etc and change the category to Scientific.

[I don't know how to embed the spreadsheet into the post.]

How can I get Excel to ignore that format/style/category and output it how it needs to be - 01E0205, 01E0713, 01E0916, etc?


Thanks

 

[t2true]

When importing the csv, on page 3 of the wizard did you select the relevant column in the data preview section & then select the "Text" option above?

Yes.

 

[t2true]

try import to PowerQuery where you can change to text

Thanks for the feedback.

I am unfamiliar with PowerQuery.

 

[t2true]

It is not cross referenced, a member spotted that you had asked the question elsewhere & reported it.
Most help boards have the same rules, please read this to understand why. https://www.excelguru.ca/content.php?184

Ah, I understand. Thanks for clarifying.

 

[sandy666]

I am unfamiliar with PowerQuery.

maybe time to learn?

 

[Fluff]

Yes.

In that case I don't understand why it's still converting them to numbers, as it shouldn't

 

[SpillerBD]

In that case I don't understand why it's still converting them to numbers, as it shouldn't

I would suspect that the CSV file is opened and saved, still as a CSV file type, after Excel has doodled in it.
The user might maintain control if the default application for CSV file types is changed to NOTEPAD instead.

I can see some software Exporting to the Application the CSV file. Essentially forcing the CSV into Excel taking away the control we would prefer.

 

[xenou]

Quote Originally Posted by Fluff View Post
When importing the csv, on page 3 of the wizard did you select the relevant column in the data preview section & then select the "Text" option above?
Yes.

This should work. Are you invoking the import wizard correctly? How are you importing the file? I usually change the file extension to .txt for this, as otherwise csv files are imported directly without the wizard.

Nevermind - see that you used the data import wizard. That works just fine for me. Not at all clear why it would fail in your case (as I've never known it to fail before). Do you have a sample csv file for testing?

Also for what its worth if you have a known format or rule that can use to convert back the scientific notation you can always reformat the data after import.