Alleen cijfers uit tekst extraheren (tekenreeks)
In Excel kunt u formules gebruiken om het getal uit een waarde te extraheren met tekst en getallen gecombineerd. In het volgende voorbeeld hebben we een formule gebruikt om 1984 te verkrijgen uit de waarde “TPS1984”.
Formule om getallen uit tekst te extraheren
=TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),""))In deze tutorial zullen we deze formule in detail begrijpen. En om het te begrijpen, moeten we het in verschillende delen verdelen:
- Ten eerste hebben we de LEN-functie om het aantal tekens in de waarde te verkrijgen.
- Daarna gebruikt INDIRECT een celverwijzing met behulp van de 1 en 7 (geretourneerd door de LEN).
- Vervolgens gebruikt de RIJ-functie INDIRECT en retourneert een reeks getallen beginnend met 1 en eindigend met 7.
- Nu zal MID deze array gebruiken en een nieuwe retourneren met de waarden van de cel waarnaar u verwees.
- Vervolgens hebben we een eenvoudige vermenigvuldigingsformule om de array met 1 te vermenigvuldigen. Met deze eenvoudige vermenigvuldiging krijg je een nieuwe array waarin je #WAARDE! De fout voor tekstwaarden en getallen blijft intact.
- Vanaf hier converteert de IFERROR-functie de foutwaarden naar lege waarden.
- En aan het einde combineert TEXTJOIN de waarden, en het resultaat bevat alleen cijfers.
De bovenstaande formule werkt alleen voor Excel 2019-versie en hoger. Dit komt omdat we TEXTJOIN gebruiken, niet beschikbaar in eerdere versies.
Als u een waarde voor een deel van de formule wilt, selecteert u dat deel en drukt u op de sneltoets F9.
TEXTJOIN is een vluchtige functie die van waarde verandert wanneer u een cel in het werkblad bijwerkt. Dit kan uw werkmap een beetje traag maken.
Maar wat als ik een eerdere versie gebruik (2007, 2010, 2013, 2016)
Als u eerdere versies gebruikt, moet u een andere formule gebruiken. Een formule als deze:
=IF(SUM(LEN(A1)-LEN(SUBSTITUTE(A1, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1))* ROW(INDIRECT("$1:$"&LEN(A1))),0), ROW(INDIRECT("$1:$"&LEN(A1))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A1)))/10),"") =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)De twee bovenstaande formules werken goed met verschillende soorten waarden. Ik heb het op de volgende manier getest.
In cel A1 hierboven hebben we een complexe waarde die bestaat uit tekst, cijfers en symbolen en we hebben alleen cijfers in het resultaat.