Stopp Excel fra å konvertere dataene til vitenskapelig notasjon når du importerer data fra en CSV- eller TXT-fil.
Se på video
- Du har mellomrom som TRIM ikke fjerner
- Du har et delenummer som slutter med e og et siffer
- Du har et delenummer med mer enn 15 sifre
- Hvis du importerer som en CSV-fil, endres delenumrene til Scientific Notation
- Hvordan vise utvidelser i Windows Utforsker
- Hvis du importerer ved å åpne en .txt-fil, kan du prøve å spesifisere at disse kolonnene er tekst, men
- når du finner / erstatter det ikke-bryte rommet (karakter 160), endres delenumrene til vitenskapelig notasjon
- Løsningen er å bruke Data, Få eksterne data, fra tekst.
- Denne kommandoen mangler imidlertid fra Office 365, etter å ha blitt erstattet av Get & Transform.
- Hvis du ikke har Fra tekst, høyreklikker du verktøylinjen for hurtig tilgang og tilpasser
- Bytt til Alle kommandoer øverst til venstre. Finn fra tekst (arv) og legg til i QAT
- Du kan åpne en CSV-fil ved å bruke Fra tekst, og den lar deg gå gjennom tekstimportveiviseren
- I trinn 2 i veiviseren, spesifiser både komma og alt = "" + 0160 som tilpasset. Behandle påfølgende avgrensere som en.
- Takk til Jan Karel: her
- Ikke glem å stemme: her
Videoutskrift
Lær Excel fra Podcast, Episode 2087: Prevent Scientific Notation on Import
Wow! Vi kommer til å håndtere mange forskjellige spørsmål i dag. Så flere sender inn lignende problemer. Enten har vi et delenummer - Se dette delenummeret her hvor det siste til siste sifferet er. Alt er numerisk, men det nest siste sifferet er en bokstav: D, E, F. Disse E-ene kommer til å være et problem. Disse E-ene kommer til å komme inn som vitenskapelig notasjon eller noe delenummer som er helt numerisk lenger enn 15 sifre, vil ha et problem.
Også mange har spurt meg om - eller fått data fra dette nettbaserte systemet, og det er mellomrom før og etter som ikke går bra med TRIM. Så hvis du har noen av disse tre problemene, potensielt, skal jeg hjelpe deg.
Greit nå, det første vi skal gjøre her er at vi skal se på dette, CSV-filen, ok? Og jeg skal bare dobbeltklikke for å åpne det; og fordi det er en CSV-fil, gidder de ikke å ta oss gjennom veiviseren for tekstimport, som er fryktelig, ikke sant? Og så ser du at vi har noen problemer. Først og fremst, på grunn av E-er, kom alt med en E inn i vitenskapelig notasjon. Og hvis vi prøver å fikse dette, gå tilbake til et nummer eller noe sånt. Vi har tapt. Vi har mistet ting. Samme med ting over 16 tegn, selv om du endrer disse til tall, vil du være i trøbbel fordi du har mistet de siste sifrene, ok. Det er bare fryktelig.
Og dette - Å, dette er flott! Dette er fantastisk. Se på dette, uh ja, det kom inn uten å konvertere. Åh, men det er ledende og etterfølgende mellomrom, våre VLOOKUPS kommer ikke til å fungere. Greit nå, det første vi vil gjøre er å finne ut hva disse ledende og etterfølgende plassene er, for hvis jeg prøver å = trimme, = trimme av den tingen, går den ikke bort. Og hvordan kan jeg fortelle at det ikke forsvinner? Fordi jeg kan sammenkoble en stjerne = “” før og - Og du ser at det fortsatt er noe der, ok? Og når det skjer, er det fremdeles noe der. Du vet hvorfor ikke TRIM lager - TRIM skal kvitte seg med ledende og etterfølgende rom. Så her er hva jeg gjør. Når det gjelder = KODEN til VENSTRE for dette, 1 for å se hva det er, og det er en karakter 160. Å, det er ikke det vi skal få.Denne gangen et vanlig gammelt rom der, bare et rom. Hit mellomromstasten, det er en karakter 32. Det er et ekte rom som TRIM blir kvitt. Hva pokker er en 160? En 160 er et rom som ikke bryter. Dette er veldig populært rundt nettsteder, for hvis du bygger en webside, legger du plass til plass. Vel, Internet Explorer og Chrome vil bare gjøre det til en enkelt plass. Men hvis du setter mellomrom som ikke er i stykker, 3 av dem, vil det faktisk beholde de tre plassene.Men hvis du setter mellomrom som ikke går i stykker, 3 av dem, vil det faktisk beholde de tre mellomromene.Men hvis du setter mellomrom som ikke går i stykker, 3 av dem, vil det faktisk beholde de tre mellomromene.
Ok, så nå, her er den frustrerende tingen vi må gjøre. For å bli kvitt disse 160 mellomrommene, må du enten være i stand til å skrive et tegn 160, noe som betyr at du må ha et numerisk tastatur. Greit nå, vær oppmerksom mens jeg gjør dette. Jeg skal holde nede alt = "" -tasten og nå med det numeriske tastaturet 0160, la gå og der er det. Ser du, det kom akkurat inn, ok? Nå, hvis du er heldig nok til at du har et numerisk tastatur, vil dette problemet her være Ctrl + H, i Finn hva som holder Alt + 0160 nede, la gå og Erstatt med ingenting, Erstatt alt. Alt gjort, vi gjorde 34 erstatninger. Men jeg blir en sønn av en pistol, de endret tallene til vitenskapelig notasjon, ok. Så jeg klarte å få dem inn, men jeg har fortsatt sjansen til å bytte til vitenskapelig notasjon.
Nå, forresten, hvis du ikke har et numerisk tastatur slik at du kan skrive Alt + 0160, vil ikke tallene over toppen fungere. Glem det, kommer aldri til å jobbe. Så hvis du sårt trengte å skrive karakter 160 = CHAR (160), ikke trykk Enter, trykk F9 som vil evaluere det. OK, så nå i den cellen har jeg et enkelt mellomrom, men det er ikke et tegn 32, det er et tegn 160, og jeg holder nede Skift-tasten og trykker på venstre piltast for å velge det. Ctrl + C, nå er det på utklippstavlen mitt. Nå kommer vi hit. Velg disse to kolonnene, Ctrl + H, Finn hva: Jeg vil lime inn der Ctrl + V. Erstatt med: Ingenting. Erstatt alt, klikk Ok, klikk Lukk. Og igjen svever jeg meg under pusten fordi de konverterte alle disse til vitenskapelig notasjon.
Greit nå, det jeg vanligvis ber folk gjøre, det jeg normalt vil fortelle folk å gjøre er å komme tilbake til Windows Utforsker og konvertere det fra en CSV-fil til en .txt-fil. Nå her kan jeg ikke tilfeldigvis se det. Hvis du ikke ser det - hvis du ikke ser utvidelsene, trykker du på alt = "" -tasten og deretter Verktøy og deretter Mappealternativer og her under Vis, der det står Skjul utvidelser for kjente filtyper, fjern merket for det. Det er den verste innstillingen noensinne. Jeg slår det av hele tiden. Jeg vil se utvidelsen på den måten, så jeg kan høyreklikke og gi nytt navn og endre den til .txt. Greit nå, hva er fordelen med å komme til .txt? Å hei, det er kjempebra. Når jeg gjør en .txt, for hvis jeg går til File and Open, så blar vi til den mappen. Og jeg åpner .txt-versjonen, klikker Ok. OK nå, hei,Jeg får gå gjennom og si i hvert trinn hvilken type det er, og så kan jeg si - Ah, la oss bryte det av kommaet. Jepp, vakkert. Og neste, og akkurat her skal jeg si, ikke skru med dette. Tekst er måten å si ikke skru på med dette. Det samme her, ikke skru med dette. Disse, ikke skru med dem, tekst, tekst, tekst. Og vanligvis liker vi ikke å bruke tekst, men her hvor de endrer tallene mine, vil bruk av tekst tillate at de kommer inn, og de vil ikke være vitenskapelig notasjon. Wow! Det er kult. Og dette er måten jeg alltid foreslo å løse dette problemet, men så så jeg denne flotte artikkelen av en venn av meg, Jan Karel, JKP Application Development Services, som viste meg en strålende ny måte. En strålende ny måte. Så la meg vise deg dette. JEG'Jeg setter lenken til denne artikkelen der nede i YouTube-kommentaren. Husk å sjekke ut artikkelen.
OK, så vi kommer tilbake hit, og det vakre er at vi ikke trenger å gi nytt navn til dette fra tekst - fra CSV til tekst, fordi det vil håndtere CSV, noe som er veldig bra, for hvis vi får denne filen hver eneste dag ønsker vi å kunne takle CSV. Her er en gal ting. Hvis du er på Excel 2013 eller tidligere, vil vi gå til Data-fanen, Få eksterne data og bruk fra tekst. Men hvis du bruker Office 365, den siste versjonen av Office 365, er den delen borte. OK, så på Office 365 etter å høyreklikke opp her, og si Tilpass verktøylinjen for hurtig tilgang og den venstre tingen, velg Alle kommandoer.
Dette er en veldig lang liste, vi skal ned til F-ene. F er for From Text - se på alle disse From's, jeg trenger å finne den som sier From Text (Legacy). Det er den gamle versjonen. Nå ser, de vil at vi skal bruke Power Query, men la oss bare lage noe som vil fungere for alle. Nå som jeg har, nå som jeg har From Text Legacy, vil jeg bare komme hit til splitter nytt regneark, Sett inn regneark. Nå har vi et sted for dette å gå fra tekst, og vi vil navigere til CSV-filen vår. Klikk på Importer, og vi skal si Avgrenset. Ja! Men i trinn 2 skal jeg si at jeg vil avgrense det i kommaet. Jeg vil også avgrense det på rommet, og jeg vil avgrense det på Alt + 0160. Nå igjen, hvis du ikke har et numerisk tastatur,Jeg må bruke trikset som jeg viste deg for et par minutter siden for å kunne kopiere det og lime det inn i cellen. Og oh! Forresten, hvis du får flere ting ved siden av hverandre, til og med et komma og et Alt + 0160, så behandle de påfølgende avgrensningene som en. OK, denne teksten, faktisk alle disse, blir tekst. Vi vil ikke at de skal skru på noen av dem. De blir alle slik.
Her er den vakre tingen. Først og fremst, CSV-filer, vil få svaret på disse spørsmålene fordi vi bruker Fra tekst, og vi får si hvor vi skal legge den og Egenskaper, at vi vil lagre spørringsdefinisjonen. Og så, hver gang vi åpner denne filen, kan vi kanskje gå ut og oppdatere dataene, så denne arbeidsboken kan være innehaveren av at hver gang vi åpner denne, kommer den til å gå tilbake til CSV og huske alle våre svar og gjøre det - gjør alle trinnene. Så klikk Lukk, klikk OK, og ingenting kommer inn, og vitenskapelig notasjon, alt er endret til tekst. Og du vet, vi trenger ikke å bekymre oss for bare å dobbeltklikke på at de blir CSV-filer, fordi det lar oss spesifisere hva hvert av disse feltene er.
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 trikset fra vennen min, Jan Karel, og ikke glem å stemme på excel.uservoice.com. Vel hei, jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned fil
Last ned eksempelfilen her: Podcast2087.xlsm
