Er is geen ingebouwde functie in Excel om getallen uit een tekenreeks in een cel te extraheren (en vice versa - verwijder het numerieke gedeelte en extraheer het tekstgedeelte uit een alfanumerieke tekenreeks).
Dit kan echter worden gedaan met behulp van een Excel-functie of een eenvoudige VBA-code.
Laat me je eerst vertellen waar ik het over heb.
Stel je hebt een dataset zoals hieronder en je wilt getallen uit strings halen (zoals hieronder):
De methode die u kiest, hangt ook af van de versie van Excel die u gebruikt:
- Voor versies van Excel vóór 2016 moet u een iets langere formule gebruiken
- Voor Excel 2016 kunt u de nieuw geïntroduceerde TEXTJOIN-functie gebruiken
- VBA-methode beschikbaar voor alle versies van Excel
Inhoud
Extraheer getallen uit strings in Excel (Excel 2016-formules)
Deze formule werkt alleen in Excel 2016 omdat deze de nieuw geïntroduceerde TEXTJOIN-functie gebruikt.
Bovendien kan deze formule getallen aan het begin, einde of midden van een tekenreeks extraheren.
Merk op dat de TEXTJOIN-formule die in deze sectie wordt beschreven, alle numerieke tekens samenvoegt.Als de tekst bijvoorbeeld "10 tickets zijn $ 200" is, is het resultaat 10200.
Stel dat je een dataset hebt zoals de volgende, en je wilt getallen uit strings in elke cel halen:
De onderstaande formule geeft u het numerieke deel van een tekenreeks in Excel.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
Dit is een matrixformule, dus je moet " Control+Shift+Enter " in plaats van Enter te gebruiken.
Deze formule retourneert blanco (lege tekenreeks) als er geen getallen in de tekenreeks staan.
Hoe werkt deze formule?
Laat me deze formule opsplitsen en proberen uit te leggen hoe het werkt:
- ROW(INDIRECT(“1:”&LEN(A2))) – Dit deel van de formule geeft een reeks getallen vanaf één.De functie LEN in de formule retourneert het totale aantal tekens in de tekenreeks.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {1;2;3 ;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
- (MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1) - Dit deel van de formule retourneert een array van #VALUE's!Op tekenreeksen gebaseerde fouten of getallen.Alle teksttekens in de tekenreeks worden #WAARDE!Fouten en alle waarden blijven zoals ze zijn.Dit gebeurt wanneer we de MID-functie vermenigvuldigen met 1.
- IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"") – Bij gebruik van de IFERROR-functie worden alle #VALUE's verwijderd!Fout, alleen cijfers blijven over.De uitvoer van dit deel ziet er als volgt uit: {"";"";"";"";"";"";"";"";"";"";"";"";"";" ";"";"";1;0;0}
- =TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")) – TEXTJOIN-functie combineert nu eenvoudig tekenreeksen. Nog steeds aanwezig (alleen cijfers) en negeert lege strings.
Pro-tip:Als u de uitvoer van een deel van de formule wilt controleren, selecteert u de cel, drukt u op F2 om naar de bewerkingsmodus te gaan, selecteert u het deel van de formule dat u wilt uitvoeren en drukt u op F9.U ziet direct de resultaten.Vergeet dan niet op Control + Z te drukken of op Escape te drukken.Druk niet op enter.
U kunt dezelfde logica ook gebruiken om delen van tekst uit alfanumerieke tekenreeksen te extraheren.Hier is de formule om het tekstgedeelte uit de string te halen:
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))
Een kleine wijziging in deze formule is dat de ALS-functie wordt gebruikt om de array die we van de MID-functie krijgen op fouten te controleren.Als het een fout is, behoudt u de waarde, anders vervangt u deze door blanco.
Gebruik vervolgens TEXTJOIN om alle teksttekens te combineren.
注意: Hoewel deze formule geldig is, gebruikt deze een vluchtige functie (de INDIRECT-functie).Dit betekent dat als u het met een enorme dataset gebruikt, het enige tijd kan duren voordat u resultaten krijgt.U kunt het beste een back-up maken voordat u deze formule in Excel gebruikt.
Extraheer getallen uit strings in Excel (voor Excel 2013/2010/2007)
Als je Excel 2013. 2010 of 2007 hebt, kun je geen TEXTJOIN-formules gebruiken, dus je moet hiervoor complexe formules gebruiken.
Stel dat je een dataset hebt zoals hieronder en je wilt alle getallen in strings in elke cel extraheren.
De onderstaande formule doet dit:
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7"," 8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2)))), 1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT ("$1:$"&LEN(A2)))/10),"")
Deze formule retourneert blanco (lege tekenreeks) als er geen getallen in de tekenreeks staan.
Hoewel dit een matrixformule is, moet uGeen behoefteGebruik "Control-Shift-Enter" om het te gebruiken.Een eenvoudige invoer werkt voor deze formule.
Krediet voor deze formule gaat naar het geweldigeMijnheer Excel-forum.
Nogmaals, deze formule extraheert alle getallen in de tekenreeks, ongeacht de positie.Als de tekst bijvoorbeeld "10 tickets zijn $ 200" is, is het resultaat 10200.
注意: Hoewel deze formule geldig is, gebruikt deze een vluchtige functie (de INDIRECT-functie).Dit betekent dat als u het met een enorme dataset gebruikt, het enige tijd kan duren voordat u resultaten krijgt.U kunt het beste een back-up maken voordat u deze formule in Excel gebruikt.
Scheid tekst en cijfers in Excel met VBA
Als u vaak tekst en cijfers moet scheiden (of cijfers uit tekst wilt halen), kunt u ook de VBA-methode gebruiken.
Het enige dat u hoeft te doen, is een aangepaste door de gebruiker gedefinieerde functie (UDF) in Excel maken met behulp van eenvoudige VBA-code en vervolgens die VBA-formule gebruiken in plaats van een lange en complexe formule.
Laat me je laten zien hoe je twee formules in VBA kunt maken - een om getallen te extraheren en een om tekst uit strings te extraheren.
Extraheer getallen uit strings in Excel (met VBA)
In dit deel laat ik je zien hoe je een aangepaste functie maakt om alleen het numerieke deel van een string te krijgen.
Hier is de VBA-code die we zullen gebruiken om deze aangepaste functie te maken:
Functie GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Resultaat Eindfunctie
Hier zijn de stappen om deze functie te maken en deze vervolgens in een werkblad te gebruiken:
U kunt nu de GetText-functie op het werkblad gebruiken.Aangezien we al het zware werk in de code zelf hebben gedaan, hoeft u alleen de formule =GetNumeric(A2) te gebruiken.
Dit geeft je meteen het numerieke deel van de string.
Merk op dat aangezien de werkmap nu VBA-code bevat, u deze moet opslaan met de extensie .xls of .xlsm.
Als u deze formule vaak moet gebruiken, kunt u deze ook opslaan in uw persoonlijke macrowerkmap.Hierdoor kunt u deze aangepaste formule gebruiken in elke Excel-werkmap die u gebruikt.
Extraheer tekst uit string in Excel (met VBA)
In dit deel laat ik je zien hoe je een aangepaste functie maakt om alleen het tekstgedeelte van een tekenreeks te krijgen.
Hier is de VBA-code die we zullen gebruiken om deze aangepaste functie te maken:
Functie GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength Indien niet (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1 ) Volgende i GetText = Resultaat Eindfunctie
Hier zijn de stappen om deze functie te maken en deze vervolgens in een werkblad te gebruiken:
U kunt nu de GetNumeric-functie in het werkblad gebruiken.Aangezien we al het zware werk in de code zelf hebben gedaan, hoeft u alleen de formule =GetText(A2) te gebruiken.
Dit geeft je meteen het numerieke deel van de string.
Merk op dat aangezien de werkmap nu VBA-code bevat, u deze moet opslaan met de extensie .xls of .xlsm.
Als u deze formule vaak moet gebruiken, kunt u deze ook opslaan in uw persoonlijke macrowerkmap.Hierdoor kunt u deze aangepaste formule gebruiken in elke Excel-werkmap die u gebruikt.
