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


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.