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 met nieuw speelgoed, ik bedoel functies.
Excel 2019 en 365 introduceren enkele nieuwe functies (TEXTJOIN en SEQUENCE) die het verwijderen van numerieke tekens kunnen vergemakkelijken en alleen niet-numerieke waarden in een nieuwe cel kunnen ophalen. We zullen formules gebruiken die ons daarbij kunnen helpen, gemakkelijker.
Generieke formule
| =TEXTJOIN("",WAAR,INDIEN(ISERROR(MIDDEN(door elkaar gegooide_tekst,VOLGORDE(Aantal tekens),1)+0),MIDDEN(door elkaar gegooide_tekst,VOLGORDE(Aantal tekens),1),""))) |
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 numerieke tekens en extraheer alle alfabetten en niet-numerieke tekens.
Dus hier hebben we wat alfanumerieke tekst. Deze tekst bevat enkele cijfers en enkele niet-numerieke tekens. Ik moet numerieke tekens verwijderen en alleen alfabetten en andere tekenwaarden krijgen in de D-kolom.
Ik verwacht niet dat het totale aantal tekens in door elkaar gegooide tekst meer dan 100 zal zijn. Dus de waarde van NumChars is hier 100. Indien nodig kunt u dit aantal verhogen of verlagen.
Pas de bovenstaande generieke formule hier toe om de numerieke tekens te verwijderen.
| =TEXTJOIN("",WAAR,INDIEN(ISERROR(MIDDEN(B3,VOLGORDE(100),1)+0),MIDDEN(B3,VOLGORDE(100),1),""))) |
Wanneer u op de enter-knop drukt, worden alle numerieke tekens verwijderd en blijven alleen alfabetische waarden over. Sleep deze formule naar beneden om getallen uit de tekenreeks te verwijderen uit alle cellen in kolom B.
Hoe werkt het?
Laten we eerst eens kijken hoe deze formule stap voor stap wordt opgelost.
| 1-> TEXTJOIN("",WAAR,INDIEN(ISERROR(MIDDEN(B3,VOLGORDE(100),1)+0),MIDDEN(B3,VOLGORDE(100),1),""))) |
| 2-> TEXTJOIN("",WAAR,INDIEN(ISERROR(MIDDEN("This1 is… site",{1,2,3,… 100},1)+0),MIDDEN(B3,VOLGORDE(100),1),""))) |
| 3-> TEXTJOIN("",WAAR,INDIEN(ISERROR({"T";"h";"i";"s";" ","1"… "";""}+0),MIDDEN(B3,VOLGORDE(100),1),""))) |
| 4-> TEXTJOIN("",WAAR,INDIEN(ISERROR({#WAARDE!;#WAARDE!;#WAARDE!;#WAARDE!;1… ;#WAARDE!}),MIDDEN(B3,VOLGORDE(100),1),""))) |
| 5-> TEXTJOIN("",WAAR,INDIEN({TRUE;TRUE;TRUE;TRUE;FALSE… ;TRUE},MIDDEN(B3,VOLGORDE(100),1),""))) |
| 6-> TEXTJOIN("",WAAR,{"Dit";""… .;""}) |
| 7-> "Dit is de nummer één website" |
Voor het leesgemak heb ik niet de hele array geschreven. Ik gebruikte punten (… ) om lange arrays aan te geven.
Zoals je kunt zien, begint de formule van binnenuit op te lossen. Eerst wordt de SEQUENTIE-functie opgelost. Aangezien we 100 zijn gepasseerd als het aantal tekens. Het retourneert een reeks getallen beginnend van 1 tot 100.
Deze array wordt als startnummer aan de MID Function aangeboden. De mid-functie gaat naar elke index in string en splitst elk teken in een array. Je kunt dat zien in stap 3. Ik heb niet de hele array getoond omdat het te veel ruimte in beslag neemt. {"T";"h";"i";"s";" ","1"… "";""}
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. {#WAARDE!;#WAARDE!;#WAARDE!;#WAARDE!;1…;#WAARDE!}
Deze array wordt geserveerd aan de ISERROR functie. Zoals u weet, retourneert de functie ISERROR TRUE voor fouten en FALSE voor niet-foutwaarden. Daarom krijgen we een array van TRUE en FALSE. TRUE voor niet-numerieke tekens en FALSE van cijfers. {TRUE;TRUE;TRUE;TRUE;FALSE… ;TRUE}.
Hetzelfde gebeurt met TRUE sectie-instructies van IF (MIDDEN(B3,VOLGORDE(100),1)). Het retourneert een array van alle tekens van alfanumerieke tekenreeksen in B3.
Nu retourneert de IF-functie voor elke TRUE-instructie het overeenkomstige teken uit de array van de true-sectie. Voor ONWAAR retourneert IF blanco (""). Nu heb je een array die geen numeriek teken bevat. {"Dit";""… .;""}.
Ten slotte wordt deze array bediend door de TEXTJOIN functie. Deze functie voegt bepaalde teksten met elkaar samen, negeert de lege waarden, met een gegeven scheidingsteken. Daarom krijgen we een string die geen numerieke tekens bevat. Dit is de nummer één website.
De formule verbeteren
De bovenstaande formule gebruikt een hardgecodeerd nummer voor het verwerken van het aantal tekens (we hebben 100 genomen). Maar misschien wilt u dat het dynamisch is. In dat geval, u raadt het goed, kunt u de LEN-functie gebruiken. Het zal het exacte aantal tekens nodig hebben voor verwerking. Dus de formule zal zijn.
| =TEXTJOIN("",TRUE,IF(ISERROR(MID(door elkaar gegooide_tekst;SEQUENTIE)LEN(door elkaar gegooide_tekst),1)+0),MID(door elkaar gegooide_tekst,SEQUENTIE(LEN(door elkaar gegooide_tekst),1),""))) |
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,IF(ISERROR(MID(door elkaar gegooide_tekst;RIJ(INDIRECTE("1:"&LEN(Aantal tekens))),1)+0),MID(door elkaar gegooide_tekst,RIJ(INDIRECTE("1:"&LEN(Aantal tekens))),1),""))) |
De INDIRECT converteert de tekst ("1:100") naar het werkelijke bereik en vervolgens toont de ROW-functie alle rijnummers van 1 tot 100. (100 is slechts een voorbeeld. 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.
Hoe niet-numerieke tekens uit cellen in Excel 2019 te verwijderen: Om niet-numerieke tekens uit een alfanumerieke tekenreeks in Excel te verwijderen, gebruiken we de nieuwe functie TEXTJOIN. Een strip van niet-numerieke tekens uit een tekenreeks kan ons helpen onze gegevens op te schonen voor een betere gegevensanalyse. Dus hier is hoe je het doet
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 strings 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.