MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Prevent Scientific Notation on Import


May 01, 2017 - by Bill Jelen

Prevent Scientific Notation on Import

Stop Excel from converting your data to scientific notation when you import data from a CSV or TXT file.

Watch Video

  • 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
  • If you import by opening a .txt file, you can attempt to specify those columns are text, but
  • when you find/replace the non-breaking space (character 160), the part numbers change to scientific notation
  • 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.
  • Thanks to Jan Karel: here
  • Don't forget to vote: here

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2087 prevent scientific notation
  • on import now we're going to handle a
  • lot of different questions today
  • so several people send in similar
  • problems either we have a part numbers
  • part marine here where the second of the
  • last digit is all numeric but the second
  • last digit is a letter d EF 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 gonna have
  • that problem also a lot of people been
  • asking me about or getting data from
  • this web-based system and there are
  • spaces before and after that are not
  • going away with trim so if you have any
  • of those three problems potentially
  • potentially I'm going to help you all
  • right now the first thing we're going to
  • do here is we're gonna take a look at
  • this the CSV file alright 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 have some problems first
  • off because of the ease 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 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 where you've lost the last few
  • digits right 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 are vlookups are going
  • to work all right now the first thing we
  • want to do is figure out what those
  • leading and trailing spaces are because
  • if I try to equal trim equal 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 equal in
  • quotes before actually and you see
  • there's still something there right and
  • when that happens there's still
  • something there
  • you know why is trim not making cream is
  • supposed to get rid of leading and
  • trailing spaces so here's what I do as
  • for the equal code of the
  • left of this comma one to see what that
  • is and it is a character 160 that's not
  • what we should get if you just have a
  • regular old space there just a space hit
  • the space bar 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
  • popular around websites because if
  • you're building a web page you put space
  • space space my internet explorer and
  • chrome are just going to make that be a
  • single space book you put non-breaking
  • spaces three of those then it's actually
  • going to keep the three spaces all right
  • 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
  • all right now pay attention as I do this
  • I'm going to load on the Alt key and now
  • with the numeric keypad zero one six
  • zero let go and there it is see it just
  • popped in right 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 fine what hold
  • down alt zero one six zero 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 all to 0-1-6-0 using the
  • numbers across the top will not work
  • forget it now we're going to work so if
  • you desperately needed to type character
  • 160 equals C har of 160 don't press
  • ENTER press f9 which will evaluate that
  • right so now in that cell I have a
  • single space but it's not a character 32
  • is a character 160 and I'm gonna 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
  • control H find what I will paste it or
  • control V replace with nothing replace
  • all click OK click close and again I'm
  • soaring under my breath because they can
  • verted 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 dot 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
  • because the extension that way I'm free
  • to right-click and rename and change it
  • to dot txt all right now what's the
  • benefit of getting to txt oh hey it's
  • awesome when I do a dot exe because then
  • if I go to file and open and we'll
  • browse to that folder and I open the dot
  • txt version click OK
  • all right now hey I get to go through
  • and say in each step what type it is
  • and so I can say let's break it by the
  • comma 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 the
  • same thing you're telling screw with
  • this these don't screw with them text
  • text text now usually we don't like to
  • use text but here where they're 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
  • Yann Carroll 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
  • comments 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 Tex 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 in Excel
  • 2013 or earlier we want to go to the
  • data tab get external data and use
  • from text but if you're in office 365
  • the latest version of office 365 that
  • section is gone all right so in 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 apps apps for from text look
  • at all these Fromm's I need to find the
  • one that says from text legacy that's
  • the old version uh see they want 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 we'll just come here to a 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 two 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
  • 0-1-6-0
  • 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 although by
  • the way if you get multiple things next
  • to each other even a comma and an alt 0
  • 1 6 0 then treat those consecutive
  • limiters 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 say like that now
  • here's the beautiful thing first off CSV
  • files would get to answer these
  • questions because we use from text and
  • 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 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 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'd have to worry about just
  • double-clicking the oboz csv file
  • because it's allowing us to specify what
  • each of those fields are
  • all right my book power Excel with mr.
  • Excel has a lot of different tips 617
  • Excel mystery self 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 will Krim will 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 the 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 all right so I showed
  • you how to change the extensions in
  • Windows Explorer when you open a text
  • file yeah sure the things coming to 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 it's missing from
  • office 365 replaced by getting 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 two the wizard we're specifying a
  • comma and a space and then all T Row 1 6
  • 0 as custom treat consecutive delimiter
  • says one this awesome trick from my
  • friend John Carroll and don't forget to
  • vote excel like user voice calm why I
  • want to thank you for stopping by we'll
  • see you next time for another net cast
  • from MrExcel

Download File

Download the sample file here: Podcast2087.xlsm

Title Photo: braetschit / Pixabay