We hebben geleerd hoe we numerieke waarden uit een cel kunnen verwijderen in Excel 2016 en ouder. De formules die we gebruikten waren een beetje ingewikkeld, maar nu zijn Excel 2019 en 365 in het spel.
Excel 2019 en 365 introduceren enkele nieuwe functies die het verwijderen van niet-numerieke tekens vereenvoudigen en alleen numerieke waarden in een nieuwe cel ophalen. We zullen formules gebruiken die ons daarbij kunnen helpen, gemakkelijker.
Generieke formule
=TEXTJOIN("",WAAR,IFERROR(MIDDEN(door elkaar gegooide_tekst,VOLGORDE(Aantal tekens),1)+0,"")) |
Door elkaar gegooide_tekst: Dit is de brontekst waaruit u alle numerieke waarden wilt extraheren.
NumChars: Dit is het totale aantal tekens dat u wilt verwerken. De jumbled_text mag niet meer tekens bevatten dan dit aantal (tekens en numeriek gecombineerd).
Laten we een voorbeeld bekijken om dingen duidelijk te maken.
Voorbeeld: verwijder niet-numerieke tekens en extraheer alle cijfers
Dus hier hebben we wat door elkaar gegooide tekst. Deze tekst bevat enkele cijfers en enkele niet-numerieke tekens. Ik moet niet-numerieke tekens verwijderen en alleen numerieke waarden in de D-kolom krijgen.
Ik verwacht niet dat het totale aantal tekens in door elkaar gegooide tekst meer dan 20 zal zijn. Dus de waarde van NumChars is hier 20. Indien nodig kunt u dit aantal verhogen.
Pas de bovenstaande generieke formule hier toe om de niet-numerieke tekens te verwijderen.
=TEXTJOIN("",WAAR,IFERROR(MIDDEN(C3,VOLGORDE(20),1)+0,"")) |
En als je op de enter-knop drukt. U krijgt alle niet-numerieke tekens verwijderd. Sleep deze formule naar beneden om tekens uit de tekenreeks uit alle cellen in kolom C3 te verwijderen.
Hoe werkt het?
Laten we eerst eens kijken hoe deze formule stap voor stap wordt opgelost.
1-> TEXTJOIN("",WAAR,IFERROR(MIDDEN(C3,VOLGORDE(20),1)+0,"")) |
2-> TEXTJOIN("",WAAR,IFERROR(MIDDEN("12asw12w123",{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},1)+0,"")) |
3-> TEXTJOIN("",WAAR,IFERROR({"1";"2";"a";"s";"w";"1";"2";"w";"1";"2";"3";"";"" ;"";"";"";"";"";"";""}+0,"")) |
4-> TEXTJOIN("",WAAR,IFERROR({1;2;#WAARDE!;#WAARDE!;#WAARDE!;1;2;#WAARDE!;1;2;3;#WAARDE!;#WAARDE!;… ;#WAARDE!}+0,"")) |
5-> TEXTJOIN("",WAAR,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
6-> "1212123" |
Zoals je kunt zien, begint de formule van binnenuit op te lossen. Eerst wordt de SEQUENTIE-functie opgelost. Aangezien we de 20 hebben gepasseerd. Het geeft een reeks getallen terug, beginnend van 1 tot 20.
Deze array wordt als startnummer aan de MID Function aangeboden. De mid-functie gaat naar elke index in string en splitst elk teken. Dat zie je in stap 3.
Vervolgens voegen we 0 toe aan elk teken. Als u in Excel een getal probeert toe te voegen aan niet-numerieke tekens, resulteert dit in #WAARDE! Fout. We krijgen dus een reeks getallen en #VALUE! Fouten. De niet-numerieke tekens zijn nu verdwenen.
De volgende IFERROR-functie vervangt alle #VALUE-fouten door "" (leeg). Nu blijven er numerieke waarden en spaties over.
Ten slotte wordt deze array geserveerd aan de functie TEXTJOIN. De TEXTJOIN-functie voegt ze samen en we krijgen de a-tekenreeks die alleen getallen bevat.
De formule verbeteren
De bovenstaande formule gebruikte een hard gecodeerd nummer voor het verwerken van het aantal tekens (we namen er twintig). Maar misschien wilt u dat het dynamisch is. In dat geval, je raadt het goed, gebruiken we de LEN-functie. Er is een exact aantal tekens nodig voor verwerking. Dus de formule zal zijn.
=TEXTJOIN("",TRUE,IFERROR(MID(door elkaar gegooide_tekst,SEQUENCE(LEN(door elkaar gegooide_tekst)),1)+0,"")) |
Hier detecteert de LEN-functie automatisch het exacte aantal tekens in de alfanumerieke reeks. Dit verlicht de last van het bepalen van het maximale aantal tekens.
Alternatief van de SEQUENTIE-functie
Als u de functie VOLGORDE niet wilt gebruiken, kunt u een combinatie van de RIJ- en INDIRECT-functie gebruiken voor het genereren van volgnummers.
=TEXTJOIN("",TRUE,IFERROR(MID(door elkaar gegooide_tekst,RIJ(INDIRECTE("1:"&LEN(jumbled_text))),1)+0,"")) |
De INDIRECT converteert de tekst ("1:20") naar het werkelijke bereik en vervolgens geeft de ROW-functie alle rijnummers van 1 tot 20 weer. (20 is bijvoorbeeld, het kan elk nummer zijn).
Dus ja jongens, dit is hoe je de niet-numerieke tekens van een alfanumerieke reeks in Excel kunt verwijderen. Ik hoop dat ik voldoende uitleg heb gegeven en dat dit artikel je heeft geholpen. Als u vragen heeft over dit onderwerp of een ander Excel/VBA-onderwerp. Tot die tijd Excelleren.
Getallen en tekst uit string splitsen in Excel 2016 en ouder: Toen we de TEXTJOIN-functie niet hadden, gebruikten we de LEFT- en RIGHT-functies met enkele andere functies om numerieke en niet-numerieke tekens uit een string te splitsen.
Extraheer tekst uit een tekenreeks in Excel met behulp van Excel's LINKER- en RECHTS-functie: Om tekst in Excel uit string te verwijderen, kunnen we Excel's LEFT en RIGHT functie gebruiken. Deze functies helpen ons snaren dynamisch te hakken.
Verwijder voorloop- en volgspaties uit tekst in Excel: Voorloop- en volgspaties zijn visueel moeilijk te herkennen en kunnen uw gegevens in de war brengen. Het verwijderen van deze tekens uit de tekenreeks is een fundamentele en belangrijkste taak bij het opschonen van gegevens. Hier leest u hoe u het gemakkelijk in Excel kunt doen.
Tekens van rechts verwijderen: Om tekens rechts van een string in Excel te verwijderen, gebruiken we de LEFT-functie. Ja, de LEFT-functie. De LEFT-functie behoudt het opgegeven aantal tekens van LEFT en verwijdert alles van de rechterkant.
Verwijder ongewenste tekens in Excel: Om ongewenste tekens uit een string in Excel te verwijderen, gebruiken we de SUBSTITUTE-functie. De SUBSTITUTE-functie vervangt de gegeven karakters door een ander gegeven karakter en produceert een nieuwe gewijzigde string.
Tekst verwijderen in Excel vanaf een positie in Excel: Om tekst van een startpositie in een string te verwijderen, gebruiken we de REPLACE-functie van Excel. Deze functie helpt ons bij het bepalen van de startpositie en het aantal te strippen tekens.
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen | Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe de Excel VERT.ZOEKEN-functie te gebruiken| Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de Excel te gebruiken? AANTAL.ALS-functie| Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? | Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.