Voorkom dat Excel uw gegevens converteert naar wetenschappelijke notatie wanneer u gegevens importeert uit een CSV- of TXT-bestand.
Bekijk video
- Je hebt spaties die TRIM niet verwijdert
- Je hebt een onderdeelnummer dat eindigt op een e en een cijfer
- U heeft een onderdeelnummer met meer dan 15 cijfers
- Als u importeert als een CSV-bestand, veranderen de onderdeelnummers in Wetenschappelijke notatie
- Extensies weergeven in Windows Verkenner
- Als u importeert door een .txt-bestand te openen, kunt u proberen aan te geven dat die kolommen tekst zijn, maar
- als u de niet-afbrekende spatie (teken 160) vindt / vervangt, veranderen de onderdeelnummers in wetenschappelijke notatie
- De oplossing is om gegevens te gebruiken, externe gegevens op te halen, uit tekst.
- Deze opdracht ontbreekt echter in Office 365 en is vervangen door Ophalen en transformeren.
- Als u Van tekst niet hebt, klikt u met de rechtermuisknop op de werkbalk Snelle toegang en past u aan
- Wijzig in de vervolgkeuzelijst linksboven in Alle opdrachten. Zoek uit tekst (oud) en voeg toe aan QAT
- U kunt een CSV-bestand openen met Van tekst en u kunt de wizard voor het importeren van tekst doorlopen
- Geef in stap 2 van de wizard zowel een komma als alt = "" + 0160 op als aangepast. Behandel opeenvolgende scheidingstekens als één.
- Met dank aan Jan Karel: hier
- Vergeet niet te stemmen: hier
Videotranscriptie
Leer Excel van Podcast, aflevering 2087: voorkom wetenschappelijke notatie bij importeren
Wauw! We gaan vandaag veel verschillende vragen behandelen. Verschillende mensen sturen dus soortgelijke problemen. Ofwel hebben we een onderdeelnummer - Zie dit onderdeelnummer hier, het tweede tot het laatste cijfer. Het is allemaal numeriek, maar het voorlaatste cijfer is een letter: D, E, F. Die E's worden een probleem. Die E's komen binnen als wetenschappelijke notatie of elk onderdeelnummer dat volledig numeriek is en langer is dan 15 cijfers, zal een probleem hebben.
Ook hebben veel mensen me naar dit webgebaseerde systeem gevraagd of gegevens opgehaald en er zijn spaties ervoor en daarna die niet goed gaan met TRIM. Dus als u een van deze drie problemen heeft, kan ik u mogelijk helpen.
Oké, het eerste dat we hier gaan doen, is dat we dit gaan bekijken, het CSV-bestand, oké? En ik ga gewoon dubbelklikken om dat te openen; en omdat het een CSV-bestand is, nemen ze niet de moeite om ons door de wizard Tekst importeren te leiden, wat vreselijk is, toch? En dus zie je dat we wat problemen hebben. Ten eerste, vanwege de E's, kwam alles met een E in wetenschappelijke notatie. En als we dit zouden proberen op te lossen, ga dan terug naar een nummer of iets dergelijks. We hebben verloren. We zijn spullen kwijt. Hetzelfde met de dingen van meer dan 16 tekens, zelfs als je deze terugzet in cijfers, zul je in de problemen komen omdat je de laatste paar cijfers bent kwijtgeraakt, oké. Het is gewoon verschrikkelijk.
En dit - Oh, dit is geweldig! Dit is geweldig. Kijk hier eens, uh ja, het kwam binnen zonder te converteren. Oh, maar er zijn voorloop- en volgspaties, onze VLOOKUPS gaan niet werken. Oké, het eerste dat we willen doen, is uitzoeken wat die voorloop- en volgspaties zijn, want als ik probeer te = trimmen, = trimmen van dat ding, gaat het niet weg. En hoe weet ik dat het niet weggaat? Omdat ik een asterisk = "" kan aaneenschakelen voor en - En je ziet dat er nog steeds iets is, oké? En als dat gebeurt, is er nog steeds iets. Je weet waarom TRIM het niet haalt - TRIM zou voorloop- en volgspaties moeten verwijderen. Dus hier is wat ik doe. Wat betreft de = CODE van LINKS hiervan, 1 om te zien wat dat is, en het is een teken 160. Oh, dat is niet wat we zouden moeten krijgen.Dit keer een gewone oude ruimte daar, alleen een ruimte. Raak de spatiebalk, dat is een karakter 32. Dat is een echte ruimte waar TRIM zich van ontdoet. Wat is in godsnaam een 160? Een 160 is een vaste ruimte. Dit is echt populair bij websites, want als je een webpagina bouwt, plaats je ruimte ruimte ruimte. Welnu, Internet Explorer en Chrome gaan er maar één ruimte van maken. Maar als je niet-afbrekende spaties plaatst, 3 daarvan, dan behoudt het feitelijk de 3 spaties.Maar als je niet-afbrekende spaties plaatst, 3 daarvan, dan behoudt het feitelijk de 3 spaties.Maar als je niet-afbrekende spaties plaatst, 3 daarvan, dan behoudt het feitelijk de 3 spaties.
Oké, dus nu, hier is het frustrerende dat we moeten doen. Om van die 160 spaties af te komen, moet je ofwel een teken 160 kunnen typen, wat betekent dat je een numeriek toetsenbord moet hebben. Oké, let goed op terwijl ik dit doe. Ik ga de alt = "" -toets ingedrukt houden en nu met het numerieke toetsenbord 0160, laat los en klaar is Kees. Zie je, het kwam gewoon binnen, oké? Als je het geluk hebt dat je een numeriek toetsenbord hebt, dan is dit probleem hier Ctrl + H, in Zoeken naar wat houd Alt + 0160 ingedrukt, laat los en vervang door niets, vervang alles. Helemaal klaar, we hebben 34 vervangingen gemaakt. Maar ik zal een zoon van een geweer zijn, ze hebben die cijfers veranderd in wetenschappelijke notatie, oké. Dus ik heb die erin kunnen krijgen, maar ik heb nog steeds een kans om over te schakelen naar wetenschappelijke notatie.
Als je geen numeriek toetsenbord hebt, zodat je Alt + 0160 kunt typen, zal het gebruik van de cijfers bovenaan niet werken. Vergeet het maar, ik ga nooit werken. Dus als je het hard nodig hebt om teken 160 = CHAR (160) te typen, druk dan niet op Enter, maar druk op F9, wat dat zal evalueren. Oké, dus nu in die cel heb ik een enkele spatie, maar het is geen teken 32, het is een teken 160 en ik houd de Shift-toets ingedrukt en druk op de linkerpijltoets om dat te selecteren. Ctrl + C, dat staat nu op mijn klembord. Nu, we komen hierheen. Kies deze twee kolommen, Ctrl + H, Zoek wat: ik plak daar Ctrl + V. Vervangen door: niets. Alles vervangen, klik op Ok, klik op Sluiten. En nogmaals, ik zweef zachtjes omdat ze die allemaal hebben omgezet in wetenschappelijke notatie.
Oké, wat ik mensen normaal gesproken vertel te doen, wat ik mensen normaal gesproken zou vertellen, is terug te gaan naar Windows Verkenner en dat van een CSV-bestand naar een .txt-bestand te converteren. Nu hier, ik kan het niet toevallig zien. Als u dat niet doet, als u de extensies niet ziet, drukt u op de alt = "" -toets en vervolgens op Tools en vervolgens op Mapopties en hier onder Weergave, waar staat Verberg extensies voor bekende bestandstypen, schakelt u dat uit. Dat is de slechtste setting ooit. Ik zet dat de hele tijd uit. Ik wil de extensie op die manier zien, ik ben vrij om met de rechtermuisknop te klikken en de naam te wijzigen en deze te wijzigen in .txt. Oké, wat is het voordeel om naar .txt te gaan? Oh hey, het is geweldig. Als ik een .txt doe, want als ik dan naar Bestand en Open ga, bladeren we naar die map. En ik open de .txt-versie, klik op Ok. Oké, héIk mag doornemen en bij elke stap zeggen welk type het is en dus kan ik zeggen - Ah, laten we het door de komma breken. Ja, mooi. En vervolgens, en hier ga ik zeggen: rotzooi hier niet mee. Tekst is de manier om te zeggen: doe dit niet. Hetzelfde hier, doe hier niet mee. Deze, rotzooi er niet mee, tekst, tekst, tekst. En meestal houden we er niet van om tekst te gebruiken, maar hier waar ze mijn nummers veranderen, zal het gebruik van tekst ervoor zorgen dat die binnenkomen en het zal geen wetenschappelijke notatie zijn. Wauw! Dat is geweldig. En dit is de manier waarop ik altijd voorstelde om dit probleem op te lossen, maar toen zag ik dit geweldige artikel van een vriend van mij, Jan Karel, JKP Application Development Services, dat me een briljante nieuwe manier liet zien. Een schitterende nieuwe manier. Dus laat me je dit laten zien. IK'Ik zet de link naar dit artikel daar beneden in de YouTube-reactie. Bekijk zeker het artikel.
Oké, dus we komen hier terug en het mooie is dat we dit niet hoeven te hernoemen van tekst- van CSV naar tekst omdat het omgaat met CSV, wat echt goed is, want als we dit bestand krijgen elke dag willen we kunnen omgaan met CSV. Nu, hier is een gek ding. Als u Excel 2013 of eerder gebruikt, willen we naar het tabblad Gegevens gaan, Externe gegevens ophalen en Van tekst gebruiken. Maar als u Office 365 gebruikt, de nieuwste versie van Office 365, is dat gedeelte verdwenen. Oké, dus in Office 365 door hier met de rechtermuisknop op te klikken en de werkbalk Snelle toegang aanpassen te zeggen en links te kiezen voor Alle opdrachten.
Dit is echt een lange lijst, we gaan naar de F's. F's voor From Text - kijk naar al deze From's, ik moet degene vinden die From Text (Legacy) zegt. Dat is de oude versie. Kijk, ze willen dat we Power Query gebruiken, maar laten we gewoon iets maken dat voor iedereen werkt. Nu dat ik heb, nu ik From Text Legacy heb, kom ik hier naar Gloednieuw werkblad, Werkblad invoegen. Nu hebben we een plek om dit van tekst te laten gaan en zullen we naar ons CSV-bestand navigeren. Klik op Importeren en we zeggen gescheiden. Ja! Maar in stap 2 ga ik zeggen dat ik het wil afbakenen bij de komma. Ik wil het ook afbakenen in de ruimte en ik wilde het afbakenen op Alt + 0160. Nogmaals, als u geen numeriek toetsenbord heeft, kunt u 'Ik zal de truc moeten gebruiken die ik je een paar minuten geleden heb laten zien om dat te kunnen kopiëren en in die cel te plakken. En oh! Trouwens, als je meerdere dingen naast elkaar krijgt, zelfs een komma en een Alt + 0160, behandel die opeenvolgende scheidingstekens dan als één. Oké, deze tekst, eigenlijk al deze, wordt tekst. We willen niet dat ze met een van die dingen gaan rotzooien. Ze blijven allemaal zo.
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.
Deze geweldige truc van mijn vriend, Jan Karel, en vergeet niet te stemmen op excel.uservoice.com. Nou hey, ik wil je bedanken voor het langskomen. We zien je de volgende keer voor een nieuwe netcast van.
Download bestand
Download het voorbeeldbestand hier: Podcast2087.xlsm
