Excel Import Intelligence: Prevent Scientific Notation on Import - Episode 2087

Microsoft Excel Tutorial: If you import as a CSV file, the part numbers change to Scientific Notation

Welcome to another episode of the MrExcel Podcast, where we tackle a variety of Excel questions and problems. In this episode, we will be addressing several common issues that have been sent in by our viewers. These include problems with part numbers that contain letters, data from web-based systems with leading and trailing spaces, and long numeric values that are being converted to scientific notation.

To demonstrate these issues, we will be using a CSV file that has been imported into Excel. As many of you know, the Text Import Wizard can be a nightmare to navigate, especially when dealing with these specific problems. In our example, we can see that the part numbers with letters are being converted to scientific notation, and the long numeric values are being truncated. Additionally, the leading and trailing spaces are causing issues with the TRIM function.

To solve these problems, we will first try the traditional method of converting the CSV file to a .txt file and then importing it into Excel. However, this method also has its drawbacks, as the non-breaking spaces will still cause issues with the part numbers being converted to scientific notation. To get around this, we will use a trick from my friend Jan Karel, which involves using the From Text (Legacy) option in Excel.

For those using Office 365, this option may not be available, as it has been replaced by Get & Transform. In this case, we will need to customize the Quick Access Toolbar to add the From Text (Legacy) option. Once we have this option, we can open the CSV file and use the Text Import Wizard to specify the delimiters, including the Alt+0160 non-breaking space. This will ensure that the data is imported correctly, without any conversion to scientific notation.

Another important tip to keep in mind is to always check the file extensions in Windows Explorer. By default, Windows hides file extensions, which can cause confusion when trying to rename a file. To avoid this, we recommend unchecking the "Hide extensions for known file types" option in the Folder Options menu.

Lastly, we want to remind our viewers to vote on the Excel UserVoice page to make the Excel team aware of the issue with large numbers being converted to scientific notation. With enough votes, we can make a difference and improve the functionality of Excel for everyone.

We hope you found this episode helpful and informative. Don't forget to check out our book, "Power Excel with MrExcel," for more tips and tricks to solve your Excel mysteries. And as always, thank you for tuning in to the MrExcel Podcast. We'll see you next time for another episode.

#excel
#microsoft
#microsoftexcel
#exceltutorial
#excelhacks
#excelbug
#evergreen
#csvfile

This video answers these common search terms:
how to keep leading zeros in excel csv
how to keep leading zeros in excel when importing from a csv file
how to retain leading zero in excel csv file
can excel open a csv
open csv file in excel with text import wizard
override defaults when opening csv file

Table of Contents:
(00:00) Handling problems when opening csv files
(00:14) Issues with part numbers and scientific notation
(00:31) Troubles with data from web-based systems
(00:48) Solution for preventing scientific notation on import of csv
(01:28) Dealing with leading and trailing spaces
(02:48) Identifying non-breaking spaces
(03:38) Converting CSV file to .txt
(05:09) Benefits of converting to .txt
(06:10) Alternative solution using From Text (Legacy)
(08:45) Tips for handling spaces and large numbers in Excel
(09:36) Encouraging Excel team to stop changing large numbers to scientific notation
(10:00) Clicking Like really helps the algorithm

You have spaces that TRIM won't remove
You have a part number that ends with e and a digit
You have a part number with more than 15 digits
If you import as a CSV file, the part numbers change to Scientific Notation
How to show extensions in Windows Explorer
The solution is to use Data, Get External Data, From Text.
However, this command is missing from Office 365, having been replaced by Get & Transform.
If you don't have From Text, right-click the Quick Access Toolbar and Customize
In the top-left dropdown, change to All Commands. Find From Text (legacy) & add to QAT
You can open a CSV file using From Text and it will let you go through text import wizard
In step 2 of the wizard, specify both a comma and Alt+0160 as custom. Treat consecutive delimiters as one.

[Chadworthy_]

This just got better and better as the video went on!

[joem]

Fantastic Bill. Thank-you so much. And like many of your videos this one has little gems that are not the direct thrust of the lesson. In this case it was using multiple delimiters with the import wizard.

[sirlawrencet]

A) Who in the world could give this a thumbs down? B) In what decade will Microsoft make it possible to "turn off" scientific notation? SN has to be useful to .01% of Excel users and annoying to the rest.

[JimCampbell]

I've been working with domestic and International call records for over 20 years and run into this almost weekly.

My solution, when you are only going to convert 1, 2 or 3 columns of long numbers, is to load the CSV normally, then highlight the column and do the next series of key strokes (yes, i've memorized them, so I can do them quite fast)

<Alt>, a, e, <Enter>, <Enter>, t, <enter> Do NOT hold down any of these keys, simply strike the individual key.

YOU CAN ONLY CONVERT ONE COLUMN at a time with this method. This doesn't deal with the Char(160) issue. You will lose leading zeros if they were in your source file.

[excelisfun]

Awesome video filled with many fun tips!

[MatthewBlackwell]

Done on the vote. Though I wish the request wasn't just for "text" numbers. Sometimes I have an actual number that happens to be large and even when manually entered, it defaults to Sci Notation. Yes a manual entry can then be formatted to text, but why force us to do an extra step that now makes it so I can't have it formatted as a nice number with commas without having to add them in "manually" (through a formula/script). Just stick to the Data Entry Form Design 101 concept of

I work on medical software and if we converted values to scientific notation, we'd be breaking federal record keeping rules... so yeah. Don't mess with stuff.

[EngEzzadeen]

😮‍💨 I will be crazy
2 days I couldn't find a solution,
I have a file contains barcode, any solution I try, it is not working, once I close the file then open again, the numbers are converted again to scientific notation

[Big_Tex]

Bam this is great. I deal with 23-digit product numbers, I see this sort of CSV issue sometimes. Now if I could get people to stop making CSV files in the first place.

[ThalassaMavri]

Great video, thank you! I use the same method to preserve leading zeroes in numbers.

[rafiqulislamstudent]

I am very great full to you because i am looking for a solution about a problem regarding a text file with a large field converting into excel file. Thanks a lot Allah bless you.

[willhelmx]

Hi there, is this the problem i experiences, i use Fluke CSV-files where the conversion in 365 gives me exponetial nr like 3e-006, 6, 3e-005 etc, and i cannot do anything with these nr.. if i try to change them into digital, it just doesent do anything, nor will it let me make a graf, just tells med that the data is invalid??? ????- best regards / A, and a happy new year

[GJD]

Any reason you can't just copy the non-breaking space from the front of the number, before doing the replace?

[pro_ksy]

But in last trick, we still get file with xls extension. How to get these result for csv extension

[landontesar]

any update on the link to vote for not changing to scientific notation? shows account closed.

[v.vivekthamilarasan]

Need your help... When I convert scientific notation to decimal, last 4 digit becomes 0000. It's not providing accurate value.

Example - 123456789123456789
Scientific notation - 1.23457E+17
Decimal value - 123456789123456000(using trim function)

Please help

[HassanAli-kmnj]

bill jelen plz make this for mac users

[jeromejacinth]

get to the point! you know l love you like a brother Bill, but how do l get rid of scientific notation?!