Hoe een nummer of tekst uit Excel te extraheren

Hoe een nummer of tekst uit Excel te extraheren

Microsoft Excel is geweldig in het werken met zowel cijfers als tekst --- maar als je beide in dezelfde cel gebruikt, kun je problemen tegenkomen. Gelukkig kunt u getallen of tekst uit cellen extraheren om efficiënter met uw gegevens te werken. We demonstreren verschillende opties, afhankelijk van het formaat waarin uw gegevens zich momenteel bevinden.





Excel-nummers opgemaakt als tekst

Dit is een veelvoorkomende situatie en --- gelukkig --- heel gemakkelijk om mee om te gaan. Soms zijn cellen die alleen getallen bevatten onjuist gelabeld of opgemaakt als tekst, waardoor Microsoft Excel ze niet kan gebruiken in bewerkingen.





U kunt in de onderstaande afbeelding zien dat de cellen in kolom A zijn opgemaakt als tekst, zoals aangegeven door het vak voor getalnotatie. Mogelijk ziet u ook een groene vlag in de linkerbovenhoek van elke cel.





Converteer tekst naar nummer in Excel

Als u de groene vlag in de linkerbovenhoek ziet, selecteert u een of meer cellen, klikt u op het waarschuwingsteken en selecteert u Converteren naar getal .

Selecteer anders de cellen en selecteer in het menu Getalnotatie in het lint de standaard Nummer optie.



Als u meer gedetailleerde opties nodig heeft, klikt u met de rechtermuisknop op de gemarkeerde cel(len) en selecteert u Cellen opmaken , waarmee het betreffende menu wordt geopend. Hier kunt u de getalnotatie aanpassen en decimalen toevoegen of verwijderen, een scheidingsteken voor 1000 toevoegen of negatieve getallen beheren.

Vanzelfsprekend kunt u ook de hierboven beschreven opties Lint of Cellen opmaken gebruiken om een ​​getal om te zetten naar tekst, of tekst naar valuta, tijd of een ander formaat dat u wenst.





Nummeropmaak toepassen met Plakken speciaal van Excel

Om deze methode te laten werken, moet je een getal (elk getal) in een cel invoeren; het is belangrijk dat deze cel ook als een getal is opgemaakt. Kopieer die cel. Selecteer nu alle cellen die u naar de getalnotatie wilt converteren, ga naar Home > Plakken > Plakken speciaal , selecteer formaten om alleen de opmaak te plakken van de cel die u in eerste instantie hebt gekopieerd en klik vervolgens op Oke .

Met deze bewerking wordt de opmaak van de cel die u hebt gekopieerd, toegepast op alle geselecteerde cellen, zelfs op tekstcellen.





Getallen of tekst extraheren uit cellen met gemengd formaat

Nu komen we bij het moeilijke deel: getallen halen uit cellen die meerdere invoerformaten bevatten. Als je een nummer en een eenheid hebt (zoals '7 schoppen', zoals we hieronder hebben), kom je dit probleem tegen. Om het op te lossen, gaan we kijken naar een aantal verschillende manieren om cellen op te splitsen in getallen en tekst, zodat je met elk afzonderlijk kunt werken.

Cijfers scheiden van tekst

Als je veel cellen hebt die een combinatie van getallen en tekst of veelvouden van beide bevatten, kan het handmatig scheiden ervan enorm veel tijd in beslag nemen. Om het proces sneller te doorlopen, kunt u Microsoft Excel's gebruiken Tekst naar kolommen functie.

Selecteer de cellen die u wilt converteren, ga naar Gegevens > Tekst naar kolommen en gebruik de wizard om ervoor te zorgen dat de cellen correct worden weergegeven. Voor het grootste deel hoeft u alleen maar te klikken op Volgende en Finish , maar zorg ervoor dat u een passend scheidingsteken kiest; in dit voorbeeld een komma.

Als u alleen een- en tweecijferige nummers heeft, vaste breedte optie kan ook handig zijn, omdat alleen de eerste twee of drie tekens van de cel worden afgesplitst. Je kunt op die manier zelfs een aantal splitsingen maken.

Opmerking: Cellen die zijn opgemaakt als tekst zullen niet komen automatisch tevoorschijn met een getalnotatie (of omgekeerd), wat betekent dat u deze cellen mogelijk nog moet converteren zoals hierboven beschreven.

Een getal of tekst extraheren uit een string met scheidingstekens

Deze methode is een beetje omslachtig, maar werkt heel goed op kleine datasets. Wat we hier aannemen, is dat een spatie het getal en de tekst scheidt, hoewel de methode ook werkt voor elk ander scheidingsteken.

De belangrijkste functie die we hier zullen gebruiken, is LEFT, waarmee de meest linkse tekens uit een cel worden geretourneerd. Zoals je kunt zien in onze dataset hierboven, hebben we cellen met getallen van één, twee en drie tekens, dus we moeten de meest linkse één, twee of drie tekens uit de cellen teruggeven. Door LINKS te combineren met de ZOEK functie , kunnen we alles teruggeven aan de linkerkant van de ruimte. Hier is de functie:

=LINKS(A1, ZOEKEN(' ', A1, 1))

Hiermee wordt alles teruggezet naar de linkerkant van de ruimte. Gebruik de vulgreep om de formule op de rest van de cellen toe te passen, dit is wat we krijgen (je kunt de formule zien in de functiebalk bovenaan de afbeelding):

Zoals je kunt zien, hebben we nu alle getallen geïsoleerd, zodat we ze kunnen manipuleren. Wilt u de tekst ook isoleren? We kunnen de functie RECHTS op dezelfde manier gebruiken:

=RECHTS(A1, LEN(A1)-ZOEKEN(' ', A1, 1))

Dit retourneert X tekens vanaf de rechterkant van de cel, waarbij x de totale lengte van de cel is minus het aantal tekens links van de spatie.

Nu kunt u de tekst ook manipuleren. Wil je ze nog een keer combineren? Gebruik gewoon de CONCATENATE-functie met alle cellen als invoer:

= SAMENVOEGEN (E1, F1)

Het is duidelijk dat deze methode het beste werkt als je alleen getallen en eenheden hebt, en niets anders. Als je andere celformaten hebt, moet je misschien creatief zijn met formules om alles goed te laten werken. Als je een gigantische dataset hebt, is het de tijd waard om de formule uit te zoeken!

Een getal extraheren aan het ene uiteinde van een doorlopende reeks

Wat als er geen scheidingsteken is tussen uw nummer en tekst?

Als je het nummer links of rechts van de tekenreeks extraheren , kunt u een variant van de hierboven besproken LEFT- of RIGHT-formule gebruiken:

= LINKS (A1, SOM (LEN (A1) -LEN (VERVANGING (A1, {'0', '1', '2', '3', '4', '5', '6', '7' , '8', '9'}, ''))))

= RECHTS (A1, SOM (LEN (A1) -LEN (VERVANGING (A1, {'0', '1', '2', '3', '4', '5', '6', '7' , '8', '9'}, ''))))

Hiermee worden alle getallen van links of rechts van de tekenreeks geretourneerd.

Als je het nummer van de rechterkant van de tekenreeks extraheren , kunt u ook een proces in twee stappen gebruiken. Bepaal eerst de locatie van uw eerste cijfer in de tekenreeks met behulp van de MIN-functie. Vervolgens kunt u die informatie invoeren in een variant van de RECHTS-formule, om uw cijfers van uw teksten te splitsen.

=MIN(ZOEKEN({0,1,2,3,4,5,6,7,8,9},A1&'0123456789'))

redenen waarom sociale media slecht zijn

= RECHTS (A1, LEN (A1) -B1 + 1)

Opmerking: Houd er bij het gebruik van deze formules rekening mee dat u mogelijk de kolomtekens en celnummers moet aanpassen.

Getallen extraheren aan beide uiteinden van een doorlopende reeks

Met de bovenstaande strategieën zou je in staat moeten zijn om getallen of tekst te extraheren uit de meeste gemengde cellen die problemen opleveren. Zelfs als dat niet het geval is, kunt u ze waarschijnlijk combineren met enkele krachtige tekstfuncties in Microsoft Excel om de tekens te krijgen waarnaar u op zoek bent. Er zijn echter een aantal veel gecompliceerdere situaties die om meer gecompliceerde oplossingen vragen.

Ik vond bijvoorbeeld een forumbericht waar iemand de getallen uit een string als '45t*&65/' wilde halen, zodat hij zou eindigen met '4565.' Een andere poster gaf de volgende formule als een manier om het te doen:

=SOMPRODUCT(MID(0&A1,GROOT(INDEX(ISGETAL(--MID(A1,RIJ(:),1))*

RIJ(:),0),RIJ(:))+1,1)*10^RIJ(:)/10)

Om heel eerlijk te zijn, heb ik geen idee hoe het werkt. Maar volgens de forumpost worden de cijfers uit een ingewikkelde reeks cijfers en andere tekens gehaald. Het punt is dat je met voldoende tijd, geduld en moeite getallen en tekst uit zo ongeveer alles kunt extraheren! Je moet alleen de juiste bronnen vinden.

Na nog wat Excel-tips? Hier is formules kopiëren in Excel .

Deel Deel Tweeten E-mail Uw Windows-pc opschonen met de opdrachtprompt

Als uw Windows-pc weinig opslagruimte heeft, ruim dan de rommel op met deze snelle opdrachtprompthulpprogramma's.

Lees volgende
Gerelateerde onderwerpen
  • productiviteit
  • Spreadsheet
  • Microsoft Excel
Over de auteur Tina Sieber(831 artikelen gepubliceerd)

Terwijl ze haar PhD afrondde, begon Tina in 2006 te schrijven over consumententechnologie en is ze nooit meer opgehouden. Nu ook redacteur en SEO, je vindt haar op Twitter of wandelen op een nabijgelegen pad.

Meer van Tina Sieber

Abonneer op onze nieuwsbrief

Word lid van onze nieuwsbrief voor technische tips, recensies, gratis e-boeken en exclusieve deals!

Klik hier om je te abonneren