Hoe een nummer uit een tekenreeks in Excel te extraheren

Hoe

DoorAdam Davidson

Gepubliceerd7 juni 2023

Excel is een krachtige spreadsheet, maar het kan geen wonderen verrichten. Als u gegevens importeert en die gegevens een puinhoop zijn, kan Excel er niet veel mee doen. Als u bijvoorbeeld waarden hebt die als onderdeel van een tekenreeks aan andere tekens zijn gekoppeld, kunt u er geen berekeningen op uitvoeren tenzij u ze eerst extraheert.

Gelukkig is het mogelijk om precies dat te doen, hoewel het ingewikkelder kan zijn dan je in eerste instantie zou denken. Ga als volgt te werk om een ​​getal uit een tekenreeks in Excel te extraheren.

Hoe een nummer uit het einde van een tekenreeks te extraheren met behulp van Flash Fill

Een snelle en gemakkelijke manier om een ​​getal aan het einde van een string te extraheren, is door Excel te gebruiken Flits vulling functie. Met deze functie kunt u snel een reeks cellen vullen op basis van het patroon dat in een specifieke cel is gedetecteerd. Een nadeel van deze methode is dat je de eerste extractie zelf moet doen en als je alleen een getal uit een enkele string wilt extraheren, heeft het geen zin.

In de meeste gevallen kan Flash Fill het getal correct uit het einde van een tekenreeks halen, hoewel het niet onfeilbaar is, dus u moet de resultaten altijd zorgvuldig controleren. Als het niet goed werkt, probeer dan handmatig de eerste twee of drie cellen in te vullen in plaats van alleen de eerste.

Een getal uit het einde van een tekenreeks extraheren met behulp van Flash Fill:

1. Selecteer de cel naast de eerste cel die uw tekenreeksen bevat.
   
2. Voer het nummer in dat aan het einde van de tekenreeks verschijnt.
   
3. Als al uw gegevens in een doorlopende kolom zonder gaten staan, selecteert u de cel direct onder de cel waarin u zojuist het getal hebt ingevoerd.
   
4. Als uw gegevens gaten bevatten in sommige rijen, selecteert u de eerste cel en alle cellen eronder waaruit u getallen wilt extraheren, inclusief eventuele lege rijen.
   
5. druk op Ctrl+E op Windows of Cmd+E op Mac om de Flash Fill-functie uit te voeren.
6. Uw resterende cellen worden automatisch gevuld op basis van het patroon dat Excel heeft opgemerkt. In de meeste gevallen worden de getallen aan het einde van de string correct geëxtraheerd.
   

Een getal uit het einde van een tekenreeks extraheren met behulp van een formule

Een nadeel van het gebruik van de Flash Fill-methode is dat u zelf getallen uit de tekenreeks voor de eerste rij moet extraheren. Het is mogelijk om met behulp van een formule een getal aan het einde van een tekenreeks te extraheren, hoewel de formule vrij complex is.

Een getal aan het einde van een tekenreeks extraheren met een formule:

1. Klik in de cel waar u het geëxtraheerde getal wilt weergeven.
2. Voer de volgende formule in en vervang 'B2' door de cel met uw tekenreeks:

   =WAARDE(RECHTS(B2,LENGTE(B2)-MAX(IF(ISGETAL(MIDDEN(B2,RIJ(INDIRECT("1:"&LENGTE(B2))),1)*1)=ONWAAR, RIJ(INDIRECT(" 1:"&LEN(B2))),0))))

3. druk op Binnenkomen.
4. Het nummer moet uit de string worden gehaald.
5. Om de formule toe te passen op resterende cellen, klikt u op de sleepgreep in de rechterbenedenhoek van de cel.
   
6. Sleep omlaag over de cellen waar u de formule wilt repliceren.
   
7. Laat je muis los en de formule wordt naar de geselecteerde cellen gekopieerd.
8. Deze cellen zouden nu uw geëxtraheerde nummers moeten bevatten.
   
9. Alle cellen zonder een tekenreeks om uit te extraheren, retourneren een fout. Als u in plaats daarvan een lege cel wilt retourneren, kunt u in plaats daarvan de formule gebruiken:

   =ALS.FOUT(WAARDE(RECHTS(B2,LENGTE(B2)-MAX(IF(ISGETAL(MIDDEN(B2,RIJ(INDIRECT("1:"&LEN(B2))),1)*1)=ONWAAR, RIJ(INDIRECT ("1:"&LEN(B2))),0)))),"")

10. Alle lege cellen geven nu een spatie terug.
    

Je vraagt ​​je misschien af ​​wat deze formule doet. Kortom, het genereert een array die even lang is als de string. Het plaatst elk van de karakters uit de string op volgorde in deze array. Het evalueert vervolgens elk van die tekens om te zien of ze een nummer zijn of niet. Alle tekens die getallen zijn, worden omgezet in nul. Alle tekens die geen getallen zijn, worden geconverteerd naar een getal dat de positie in de tekenreeks vertegenwoordigt.

AB5HG23 zou bijvoorbeeld converteren naar 1, 2, 0, 4, 5, 0, 0. Vervolgens zoekt het naar de hoogste waarde in deze lijst, de positie van het laatste teken dat geen getal is. Ten slotte retourneert het alle tekens na dit punt, dit zijn de cijfers aan het einde van de tekenreeks. De functie WAARDE zet dit vervolgens weer om in een getal. Eenvoudig, toch?

Een nummer extraheren vanaf het begin van een tekenreeks met behulp van Flash Fill

U kunt Flash Fill ook gebruiken om een ​​getal uit het begin van een tekenreeks te extraheren door het eerste voorbeeld zelf in te vullen en Excel het patroon te laten stoppen.

Een getal extraheren vanaf het begin van een tekenreeks met behulp van Flash Fill:

1. Selecteer de cel rechts van de eerste tekenreeks met een getal dat u wilt extraheren.
2. Voer het nummer in dat aan het begin van de tekenreeks verschijnt.
   
3. Als al uw gegevens in een doorlopende kolom zonder gaten staan, selecteert u de cel er direct onder.
   
4. Als uw gegevens hiaten bevatten, selecteert u de eerste cel en alle onderliggende cellen waaruit u getallen wilt extraheren, inclusief eventuele lege rijen.
   
5. druk op Ctrl+E op Windows of Cmd+E op Mac om Flash Fill te starten.
6. De cellen die u hebt geselecteerd, moeten nu de geëxtraheerde getallen weergeven.
   

Een getal extraheren vanaf het begin van een tekenreeks met behulp van een formule

U kunt ook een formule gebruiken om een ​​getal aan het begin van een tekenreeks te extraheren. Dit werkt op dezelfde manier als de formule in de vorige sectie, maar zoekt de positie van het eerste niet-numerieke teken en retourneert alle tekens ervoor.

Een getal extraheren vanaf het begin van een tekenreeks met behulp van een formule:

1. Selecteer de cel waarin u het geëxtraheerde getal wilt weergeven.
2. Voer de volgende formule in, waarbij u 'B18' vervangt door de cel met uw tekenreeks:

   =WAARDE(LINKS(B18,MATCH(ONWAAR, ISGETAL(MID(B18,RIJ(INDIRECT("1:"&LENG(B18)+1)),1)*1),0)-1))

3. druk op Binnenkomen en het nummer wordt geëxtraheerd.
   
4. Jij kan gebruik Automatisch aanvullen in Excel om de formule naar andere gewenste cellen te kopiëren.
5. Als u wilt dat lege rijen een leeg resultaat opleveren, gebruikt u in plaats daarvan de volgende formule:

   =ALS.FOUT(WAARDE(LINKS(B18,MATCH(ONWAAR, ISGETAL(MIDDEN(B18,RIJ(INDIRECT("1:"&LENG(B18)+1)),1)*1),0)-1))," ")

Nummers uit een string extraheren met behulp van Flash Fill

Zoals eerder vermeld, werkt Flash Fill mogelijk niet als de eerste cel niet overeenkomt met het formaat van sommige van de andere cellen. In dit geval moet u mogelijk twee of drie cellen handmatig berekenen voordat Flash Fill u het gewenste resultaat geeft.

Om getallen uit een willekeurige tekenreeks te extraheren met behulp van Flash Fill:

1. Typ in de cel naast de eerste tekenreeks handmatig de getallen in de tekenreeks.
   
2. Als al uw gegevens in een doorlopende kolom staan, selecteert u de cel er direct onder.
   
3. Als uw gegevens hiaten bevatten, selecteert u de eerste cel en alle onderliggende cellen waaruit u getallen wilt extraheren, inclusief eventuele lege rijen.
   
4. druk op Ctrl+E op Windows of Cmd+E op Mac om Flash Fill te starten.
5. De cellen zouden zich nu moeten vullen met de geëxtraheerde getallen.
   

Nummers uit een willekeurige string extraheren met behulp van een formule

U kunt een formule gebruiken om alle getallen uit een tekenreeks te extraheren, ongeacht waar ze voorkomen.

Om getallen uit een willekeurige tekenreeks te extraheren met behulp van een formule:

1. Selecteer de cel waarin u het geëxtraheerde getal wilt weergeven.
2. Voer de volgende formule in, waarbij u 'B9' vervangt door de cel met uw tekenreeks:

   =SUMPRODUCT(MID(0&B9,LARGE(INDEX(ISGETAL(--MID(B9,ROW(INDIRECT("1:"&LENGTE(B9))),1))*ROW(INDIRECT("1:"&LENGTE(B9) )),0),RIJ(INDIRECT("1:"&LENGTE(B9))))+1,1)*10^RIJ(INDIRECT("1:"&LENGTE(B9)))/10)

3. Uw formule moet alle getallen uit de tekenreeks halen.
   
4. Als u wilt dat lege cellen een lege cel retourneren in plaats van nul, gebruikt u in plaats daarvan deze formule:

   =ALS.FOUT(SUMPRODUCT(MID(0&B9,LARGE(INDEX(ISGETAL(--MID(B9,ROW(INDIRECT("1:"&LENGTE(B9))),1))*ROW(INDIRECT("1:"&LENG( B9))),0),RIJ(INDIRECT("1:"&LENGTE(B9))))+1,1)*10^RIJ(INDIRECT("1:"&LENGTE(B9)))/10)," ")

5. Jij kan gebruik Automatisch aanvullen om de formule toe te passen op andere cellen in uw spreadsheet.
6. Alle tekenreeksen die geen getallen bevatten, retourneren een nul. Je kunt leren hoe converteer een nul naar een streepje in Excel als u dit wilt wijzigen.

Breid uw Excel-vaardigheden uit

Als u leert hoe u een getal uit een tekenreeks in Excel kunt extraheren, kunt u snel de gegevens die u nodig hebt uit een lijst met tekenreeksen halen. Hoewel de vereiste formules complex kunnen zijn, hoeft u niet precies te begrijpen hoe ze werken; gewoon dat ze dat doen!

Er zijn meer handige Excel-trucs die je kunt leren om je vaardigheden naar een hoger niveau te tillen. Je kunt leren hoe cellen met tekst te tellen erin, of hoe bereken iemands leeftijd op basis van zijn geboortedatum. Je kan zelfs maak een generator voor willekeurige getallen in Excel.