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

 

[sandy666]

I assume you want put ports into the column so before paste set column format to TEXT then paste

 

[t2true]

I assume you want put ports into the column so before paste set column format to TEXT then paste

The data is imported into the spreadsheet by a report from the system. I don't copy/paste anything.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-general/1296403-stupid-scientific-category.html#post5229131

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[SpillerBD]

During the EXPORT process, is their a choice to save the file?
If so, during the save, is there an option to use a different format?

Exporting to Excel (xls, xlsx) is different than Importing by Excel.

Your problem is not unique and does source partly from Excel and partly from the programmers of the application being exported from.
Some use an export method of CSV and then the file is Opened into Excel. CSV is fine if the file is IMPORTED into Excel where you can control the data types.

For my company's ERP we had to fight with our IT to change the Export to force the keeping of Text. They used a clumsy method, but it worked. All that is needed is to precede the text with a single apostrophe. our IT set it up as ="text"

 

[t2true]

Cross posted https://www.excelforum.com/excel-general/1296403-stupid-scientific-category.html#post5229131

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

WHOOPS! Sorry about that. Yes, I did post this question in another forum here. I wasn't aware it would cross-reference (cross-post) the other forum. Other forum types that I use don't do that.

 

[Fluff]

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

 

[t2true]

During the EXPORT process, is their a choice to save the file?
If so, during the save, is there an option to use a different format?

Exporting to Excel (xls, xlsx) is different than Importing by Excel.

Your problem is not unique and does source partly from Excel and partly from the programmers of the application being exported from.
Some use an export method of CSV and then the file is Opened into Excel. CSV is fine if the file is IMPORTED into Excel where you can control the data types.

For my company's ERP we had to fight with our IT to change the Export to force the keeping of Text. They used a clumsy method, but it worked. All that is needed is to precede the text with a single apostrophe. our IT set it up as ="text"

The file format used by the system is .csv. I am not able to modify any of the data during the export, so I can't add ="text" to it. I have used the apostrophe method before. But, it involves me finding each cell and modifying it individually to add the apostrophe.
I also tried the data import wizard in Excel (suggested by another user) to change the category to text. That didn't work either.

 

[Fluff]

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?

 

[sandy666]

try import to PowerQuery where you can change to text