Stop Excel fra at konvertere dine data til videnskabelig notation, når du importerer data fra en CSV- eller TXT-fil.
Se video
- Du har mellemrum, som TRIM ikke fjerner
- Du har et delnummer, der slutter med e og et ciffer
- Du har et varenummer med mere end 15 cifre
- Hvis du importerer som en CSV-fil, ændres delnumrene til Scientific Notation
- Sådan vises udvidelser i Windows Stifinder
- Hvis du importerer ved at åbne en .txt-fil, kan du forsøge at angive, at disse kolonner er tekst, men
- når du finder / udskifter det ikke-brudende rum (karakter 160), ændres delnumrene til videnskabelig notation
- Løsningen er at bruge data, få eksterne data, fra tekst.
- Denne kommando mangler dog i Office 365, efter at være blevet erstattet af Get & Transform.
- Hvis du ikke har Fra tekst, skal du højreklikke på værktøjslinjen Hurtig adgang og tilpasse
- I rullemenuen øverst til venstre skal du skifte til Alle kommandoer. Find fra tekst (arv) & tilføj til QAT
- Du kan åbne en CSV-fil ved hjælp af Fra tekst, og den giver dig mulighed for at gennemgå guiden til tekstimport
- I trin 2 i guiden skal du angive både komma og alt = "" + 0160 som brugerdefineret. Behandl på hinanden følgende afgrænsere som en.
- Tak til Jan Karel: her
- Glem ikke at stemme: her
Videoudskrift
Lær Excel fra Podcast, afsnit 2087: Undgå videnskabelig notering ved import
Wow! Vi skal håndtere mange forskellige spørgsmål i dag. Så flere mennesker sender lignende problemer. Enten har vi et delnummer - Se dette delnummer lige her, hvor det andet til det sidste ciffer er. Det hele er numerisk, men det næstsidste ciffer er et bogstav: D, E, F. Disse E'er bliver et problem. Disse E'er kommer ind, da videnskabelig notation eller ethvert delnummer, der er helt numerisk længere end 15 cifre, vil have et problem.
Også mange mennesker har spurgt mig om - eller få data fra dette webbaserede system, og der er mellemrum før og efter, der ikke går godt med TRIM. Så hvis du har nogen af disse tre problemer, potentielt, vil jeg hjælpe dig.
Okay nu, den første ting vi skal gøre her er, at vi skal se på dette, CSV-filen, okay? Og jeg skal bare dobbeltklikke for at åbne det; og fordi det er en CSV-fil, gider de ikke at føre os gennem guiden Tekstimport, som er forfærdelig, ikke? Og så ser du, at vi har nogle problemer. For det første, på grund af E'erne, kom noget med en E ind i videnskabelig notation. Og hvis vi prøver at ordne dette, skal du gå tilbage til et nummer eller noget lignende. Vi har tabt. Vi har mistet ting. Samme ting med tingene over 16 tegn, selvom du ændrer disse tilbage til tal, vil du være i problemer, fordi du har mistet de sidste par cifre, okay. Det er bare forfærdeligt.
Og dette - Åh, det er fantastisk! Det her er fedt. Se på dette, uh ja, det kom ind uden at konvertere. Åh, men der er førende og bageste rum, vores VLOOKUPS fungerer ikke. Okay nu, den første ting, vi vil gøre, er at finde ud af, hvad disse førende og bageste rum er, for hvis jeg prøver at = trimme, = trimme af den ting, går den ikke væk. Og hvordan kan jeg se, at det ikke forsvinder? Fordi jeg kan sammenkæde en stjerne = “” før og - Og ser du, at der stadig er noget der, okay? Og når det sker, er der stadig noget der. Du ved, hvorfor laver TRIM ikke - TRIM skal slippe af med ledende og bageste rum. Så her er hvad jeg gør. Hvad angår = KODEN for VENSTRE til dette, 1 for at se, hvad det er, og det er et tegn 160. Åh, det er ikke, hvad vi skal få.Denne gang et almindeligt gammelt rum der, bare et rum. Hit mellemrumstasten, det er en karakter 32. Det er et rigtigt rum, som TRIM slipper af med. Hvad dæl er en 160? En 160 er et ikke-brudende rum. Dette er virkelig populært omkring websteder, for hvis du bygger en webside, lægger du plads plads. Nå, Internet Explorer og Chrome vil bare gøre det til et enkelt rum. Men hvis du placerer ikke-brudende mellemrum, 3 af dem, så holder det faktisk de 3 mellemrum.Men hvis du placerer ikke-brudende mellemrum, 3 af dem, så holder det faktisk de 3 mellemrum.Men hvis du placerer ikke-brudende mellemrum, 3 af dem, så holder det faktisk de 3 mellemrum.
Okay, så nu, her er den frustrerende ting, vi skal gøre. For at slippe af med disse 160 mellemrum skal du enten være i stand til at skrive et tegn 160, hvilket betyder, at du skal have et numerisk tastatur. Okay nu, vær opmærksom, mens jeg gør dette. Jeg holder nede alt = "" -tasten og slip nu med det numeriske tastatur 0160, og der er det. Se, det kom lige ind, okay? Nu, hvis du er heldig nok til at du har et numerisk tastatur, så vil dette problem herover være Ctrl + H, i Find hvad hold Alt + 0160 nede, slip og Udskift med ingenting, Udskift alt. Alt gjort, vi foretog 34 udskiftninger. Men jeg bliver en søn af en pistol, de ændrede disse tal til videnskabelig notation, okay. Så jeg var i stand til at få dem ind, men jeg har stadig en chance for at skifte til videnskabelig notation.
Nu, forresten, hvis du ikke har et numerisk tastatur, så du kan skrive Alt + 0160, fungerer det ikke ved at bruge tallene over toppen. Glem det, aldrig gå på arbejde. Så hvis du desperat havde brug for at skrive karakter 160 = CHAR (160), skal du ikke trykke på Enter, tryk på F9, som vil evaluere det. Okay, så nu har jeg i den celle et enkelt mellemrum, men det er ikke et tegn 32, det er et tegn 160, og jeg holder Shift-tasten nede og trykker på venstre piletast for at vælge det. Ctrl + C, nu er det på mit udklipsholder. Nu kommer vi herover. Vælg disse to kolonner, Ctrl + H, Find hvad: Jeg indsætter der Ctrl + V. Erstat med: Intet. Udskift alt, klik på Ok, klik på Luk. Og igen svæver jeg under vejret, fordi de konverterede alle disse til videnskabelig notation.
Okay nu, hvad jeg normalt beder folk om at gøre, hvad jeg normalt beder folk om at gøre, er at vende tilbage til Windows Stifinder og konvertere det fra en CSV-fil til en .txt-fil. Nu her kan jeg ikke tilfældigvis se det. Hvis du ikke gør det - hvis du ikke ser udvidelserne, skal du trykke på alt = "" -tasten og derefter Værktøjer og derefter Mappeindstillinger og lige her under Vis, hvor det står Skjul udvidelser for kendte filtyper, fjern markeringen for det. Det er den værste indstilling nogensinde. Jeg slukker det hele tiden. Jeg vil se udvidelsen på den måde, så jeg kan højreklikke og omdøbe og ændre den til .txt. Okay nu, hvad er fordelen ved at komme til .txt? Åh hej, det er fantastisk. Når jeg laver en .txt, for hvis jeg går til File and Open, og vi vil søge til den mappe. Og jeg åbner .txt-versionen, klik Ok. Okay nu, hej,Jeg får gå igennem og sige i hvert trin, hvilken type det er, og så kan jeg sige - Ah, lad os bryde det med kommaet. Ja, smukt. Og næste, og lige her vil jeg sige, ikke skru med dette. Tekst er den måde at sige ikke skru på med dette. Den samme ting her, ikke skru med dette. Disse, skru ikke med dem, tekst, tekst, tekst. Og normalt kan vi ikke lide at bruge tekst, men her hvor de ændrer mine numre, vil brug af tekst tillade dem at komme ind, og de vil ikke være videnskabelig notation. Wow! Det er fantastisk. Og det er sådan, at jeg altid foreslog at løse dette problem, men så så jeg denne fantastiske artikel af en af mine venner, Jan Karel, JKP Application Development Services, der viste mig en strålende ny måde. En strålende ny måde. Så lad mig vise dig dette. JEG'Læg linket til denne artikel dernede i YouTube-kommentaren. Sørg for at tjekke artiklen.
Okay, så vi kommer tilbage her, og det smukke er, at vi ikke behøver at omdøbe dette fra tekst - fra CSV til tekst, fordi det vil beskæftige sig med CSV, hvilket er virkelig godt, for hvis vi får denne fil hver eneste dag vil vi være i stand til at håndtere CSV. Her er en skør ting. Hvis du bruger Excel 2013 eller tidligere, vil vi gå til fanen Data, få eksterne data og bruge fra tekst. Men hvis du bruger Office 365, den nyeste version af Office 365, er denne sektion væk. Okay, så på Office 365 efter at højreklikke her op og sige Tilpas værktøjslinjen Hurtig adgang og den venstre ting skal vælge Alle kommandoer.
Dette er en rigtig lang liste, vi går ned til F'erne. F's for From Text - se på alle disse From's, jeg har brug for at finde den der siger From Text (Legacy). Det er den gamle version. Se nu, de vil have os til at bruge Power Query, men lad os bare oprette noget, der fungerer for alle. Nu hvor jeg har det, nu hvor jeg har From Text Legacy, vil jeg bare komme her til splinternyt regneark, indsæt regneark. Nu har vi et sted, hvor dette kan gå fra tekst, og vi navigerer til vores CSV-fil. Klik på Importer, og vi vil sige Afgrænset. Ja! Men i trin 2 vil jeg sige, at jeg vil afgrænse det i kommaet. Jeg vil også afgrænse det i rummet, og jeg ville afgrænse det på Alt + 0160. Nu igen, hvis du ikke har et numerisk tastatur, skal du 'bliver nødt til at bruge det trick, som jeg viste dig for et par minutter siden, for at kunne kopiere det og indsætte det i den celle. Og åh! Forresten, hvis du får flere ting ved siden af hinanden, endda et komma og et Alt + 0160, så behandl de på hinanden følgende afgrænsninger som en. Okay, denne tekst, faktisk alle disse, bliver tekst. Vi vil ikke have dem til at skrue med nogen af dem. De forbliver alle sådan.
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, 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.
Dette fantastiske trick fra min ven Jan Karel, og glem ikke at stemme på excel.uservoice.com. Nå hej, jeg vil gerne takke dig for at komme forbi. Vi ses næste gang til endnu en netcast fra.
Download fil
Download eksempelfilen her: Podcast2087.xlsm
