Prevent Scientific Notation on Import

Learn Excel from MrExcel Podcast, Episode 2087: Prevent Scientific Notation on Import

Wow!  We're going to handle a lot of different questions today.  So, several people send in similar problems.  Either we have a part number – See this part number right here where the second to the last digit.  It’s all numeric but the second last digit is a letter: D, E, F.  Those E's are going to be a problem.  Those E's are going to come in as scientific notation or any part number that's completely numeric longer than 15 digits will have a problem. 

Also, a lot of people have been asking me about- or getting data from this web-based system and there are spaces before and after that are not going well with TRIM.  So, if you have any of those three problems, potentially, potentially I'm going to help you. 

Alright now, the first thing we're going to do here is we're going to take a look at this, the CSV file, alright?  And I'm just going to double-click to open that; and because it's a CSV file, they don't bother to take us through the Text Import Wizard which is horrible, right?  And so, you see that we have some problems.  First off, because of the E’s, anything with an E, came into scientific notation.  And if we would try and fix this, go back to a number or something like that.  We've lost.  We've lost stuff.  Same thing with the things over 16 characters, even if you change these back to numbers you're going to be in trouble because you've lost the last few digits, alright.  It's just horrible. 

And this – Oh, this is great!  This is awesome.  Look at this, uh yeah, it came in without converting.  Oh, but there's leading and trailing spaces, our VLOOKUPS aren’t going to work.  Alright now, the first thing we want to do is figure out what those leading and trailing spaces are because if I try to =trim, =trim of that thing, it’s not going away.  And how can I tell it's not going away?  Because I can concatenate an asterisk =“ ” before and – And you see there's still something there, alright?  And when that happens, there's still something there.  You know why is TRIM not making – TRIM is supposed to get rid of leading and trailing spaces.  So, here's what I do.  As for the =CODE of the LEFT of this ,1 to see what that is, and it is a character 160.  Oh, that's not what we should get.  This time a regular old space there, just a space.  Hit the Spacebar, that's a character 32.  That's a real space that TRIM gets rid of.  What the heck is a 160?  A 160 is a non-breaking space. This is really a popular around websites because if you're building a web page, you put space space space.  Well, Internet Explorer and Chrome are just going to make that be a single space.  But if you put non-breaking spaces, 3 of those then it's actually going to keep the 3 spaces. 

Alright, so now, here's the frustrating thing that we have to do.  In order to get rid of those 160 spaces, you either have to be able to type a character 160 which means that you have to have a numeric keypad.  Alright now, pay attention as I do this.  I'm going to hold down on the Alt key and now with the numeric keypad 0160, let go and there it is.  See, it just popped in, alright?  Now, if you're lucky enough that you have a numeric keypad, well then, this problem over here is going to be Ctrl+H, in the Find what hold down Alt+0160, let go and Replace with nothing, Replace All.  All done, we made 34 replacements.  But I'll be a son of a gun, they changed those numbers to scientific notation, alright.  So I was able to get those in, but I still have a chance of changing to scientific notation. 

Now, by the way, if you don't have a numeric keypad so you can type Alt+0160, using the numbers across the top will not work.  Forget it, never going to work.  So if you desperately needed to type character 160 =CHAR(160), don’t press Enter, press F9 which will evaluate that.  Alright, so now in that cell I have a single space but it's not a character 32, it’s a character 160 and I'm going to hold down the Shift key and press the Left Arrow key to select that.  Ctrl+C, now that's on my clipboard.  Now, we'll come over here.  Choose these two columns, Ctrl+H, Find what: I will paste there Ctrl+V.  Replace with: Nothing.  Replace All, click Ok, click Close.  And again, I'm soaring under my breath because they converted all of those to scientific notation. 

Alright now, what I normally tell people to do, what I'd normally tell people to do is to come back to Windows Explorer and convert that from a CSV file to a .txt file.   Now here, I can't happen to see it.  If you don't- if you aren't seeing the extensions, press the Alt key and then Tools and then Folder Options and right here under View, where it says Hide extensions for known file types, uncheck that.  That's the worst setting ever.  I turn that off all the time.  I want to see the extension that way I'm free to right-click and Rename and change it to .txt. Alright now, what's the benefit of getting to .txt?  Oh hey, it's awesome.  When I do a .txt, ‘cause then if I go to File and Open, and we'll browse to that folder.  And I open the .txt version, click Ok.  Alright now, hey, I get to go through and say in each step what type it is and so I can say – Ah, let's break it by the Comma.  Yep, beautiful.  And Next, and right here I'm going to say, don't screw with this.  Text is the way to say don't screw with this.  Same thing here, don’t screw with this.  These, don’t screw with them, Text, Text, Text.  And usually we don't like to use Text but here where they're changing my numbers, using Text will allow those to come in and they will not be scientific notation.  Wow!  That's awesome.  And this is the way that I always suggested solving this problem but then I saw this great article by a friend of mine, Jan Karel, JKP Application Development Services, that showed me a brilliant new way.  A brilliant new way.  So let me show you this.  I'll put the link to this article down there in the YouTube comment.  Be sure to check out the article.

Alright, so we're going to come back here and the beautiful thing is we don't have to rename this from text- from CSV to text because it will deal with CSV, which is really good because if we're getting this file every single day we want to be able to deal with CSV.  Now, here's a crazy thing.  If you're on Excel 2013 or earlier, we want to go to the Data tab, Get External Data and use From Text.  But if you're on Office 365, the latest version of Office 365, that section is gone.  Alright, so on Office 365 after right-click up here, and say Customize Quick Access Toolbar and the left thing choose All Commands. 

Now, this is a really long list, we're going to go down to the F’s. F’s for From Text – look at all these From’s, I need to find the one that says From Text (Legacy).  That’s the old version.  Now see, they want us to use Power Query but let's just create something that'll work for everybody.  Now that I have, now that I have From Text Legacy, I will just come here to Brand New Worksheet, Insert Worksheet.  Now we have a place for this to go from text and we will navigate to our CSV file.  Click Import and we're going to say Delimited.  Yes!  But in step 2, I'm going to say I want to Delimit it at the Comma.  I also want to Delimit it at the Space and I wanted to Delimit it at Alt+0160.  Now again, if you don't have a numeric keypad, you're going to have to use the trick that I showed you a couple of minutes ago to be able to copy that and paste it in that cell.  And oh!  By the way, if you get multiple things next to each other, even a comma and an Alt+0160, then treat those consecutive delimiters as one.  Alright, this Text, actually all of these, are going to be Text.  We don't want them to screw with any of those.  They all stay like that. 

Now, here's the beautiful thing.  First off, CSV files, will get the answer to these questions because we use From Text and we get to say where we're going to put it and Properties, that we want to Save the query definition.  And then, every time we open this file maybe we can go out and refresh the data, so this workbook could be the holder that every time we open this, it's going to go back out to the CSV and remember all of our answers and do that- do all the steps.  So click Close, click OK and nothing comes in and scientific notation it's all been changed to Text.  And you know, we don’t have to worry about just double-clicking they’ll be CSV file because it's allowing us to specify what each of those fields are.

Alright, my book, Power Excel with MrExcel, has a lot of different tips: 617 Excel mystery solved.  Now this one, unfortunately, is not in the book but it'll be in the next edition of the book, I guarantee that. 

A lot of different things we talked about today.  If you have spaces that TRIM won’t remove, learn how to figure this out.  If you have a part number that ends in E and then a single digit or you have a partner with more than 15 digits, when you import a CSV file all of those are going to change to scientific notation.  And oh by the way, if you've been burned by this, here's another URL I'm going to paste down in YouTube videos.  Come out here and vote to make the Excel team try and tell them Excel team that you want Excel to stop changing large numbers to scientific notation.  584 votes right now.  Let's try and get that up to 600, 700, 800 or even a thousand.

Alright, so I showed you how to change the extensions in Windows Explorer.  When you open a .txt file, yeah, sure the things coming as text but as soon as you try and get rid of those non-breaking spaces, the part numbers change back to scientific notation and you have to swear again.  So we use Data, Get External Data From Text, the old legacy version.  If missing from Office 365 replaced by Get & Transform, so you have to right-click the Quick Access Toolbar and Customize in order to find it.  Now when we open a CSV file with From Text, it lets you go through the text import wizard which is better than just double-clicking the CSV file.  And step 2, the wizard will specify a comma and a space and then Alt+0160 as custom.  Treat consecutive delimiters as one. 

This awesome trick from my friend, Jan Karel, and don't forget to vote at excel.uservoice.com.  Well hey, I want to thank you for stopping by.  We'll see you next time for another netcast from MrExcel.