Vi har lärt oss hur man tar bort numeriska värden från en cell i Excel 2016 och äldre. Formlerna vi använde var lite komplexa men nu är Excel 2019 och 365 med i spelet.
Excel 2019 och 365 introducerar några nya funktioner som underlättar uppgiften att ta bort icke -numeriska tecken och hämtar endast numeriska värden i en ny cell. Vi kommer att använda formler som kan hjälpa oss att göra det, mer bekvämt.
Generisk formel
| =TEXTJOIN("",SANN,IFERROR(MITTEN(jumbled_text,SEKVENS(NumChars), 1) +0, "")) |
Jumbled_text: Detta är källtexten från vilken du vill extrahera alla numeriska värden.
NumChars: Detta är det totala antalet tecken du vill bearbeta. Jumbled_text bör inte ha fler tecken än det här numret (tecken och numeriskt kombinerat).
Låt oss se ett exempel för att göra saker tydliga.
Exempel: Ta bort icke -numeriska tecken och extrahera alla nummer
Så här har vi lite rörig text. Denna text innehåller några siffror och några icke -numeriska tecken. Jag behöver bli av med icke -numeriska tecken och bara få numeriska värden i kolumnen D.
Jag förväntar mig inte att det totala antalet tecken i blandad text ska vara mer än 20. Så värdet på NumChars är 20 här. Du kan öka detta antal om du behöver.
Använd ovanstående generella formel för att ta bort de icke -numeriska tecknen.
| =TEXTJOIN("",SANN,IFERROR(MITTEN(C3,SEKVENS(20),1)+0,"")) |
Och när du trycker på enter -knappen. Du får bort alla icke -numeriska tecken. Dra ner den här formeln för att ta bort tecken från strängen från alla celler i kolumn C3.
Hur fungerar det?
Låt oss först se hur denna formel löses steg för steg.
| 1-> TEXTJOIN("",SANN,IFERROR(MITTEN(C3,SEKVENS(20),1)+0,"")) |
| 2-> TEXTJOIN("",SANN,IFERROR(MITTEN("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("",SANN,IFERROR({"1"; "2"; "a"; "s"; "w"; "1"; "2"; "w"; "1"; "2"; "3"; ""; ""; ""; ""; ""; ""; ""; ""; ""}+0,"")) |
| 4-> TEXTJOIN("",SANN,IFERROR({1; 2;#VÄRDE!;#VÄRDE!;#VÄRDE!; 1; 2;#VÄRDE!; 1; 2; 3;#VÄRDE!;#VÄRDE!;…;#VÄRDE!}+0,"")) |
| 5-> TEXTJOIN("",SANN,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Som du kan se börjar formeln lösa inifrån. Först är SEQUENCE -funktionen löst. Eftersom vi har passerat 20. Det returnerar en rad nummer som börjar från 1 till 20.
Denna matris serveras till MID -funktionen som startnummer. Mittfunktionen går till varje index i sträng och delar varje tecken. Det kan du se i steg 3.
Därefter lägger vi till 0 till varje tecken. I excel om du försöker lägga till tal till icke -numeriska tecken resulterar det i #VÄRDE! Fel. Så vi får en rad siffror och #VÄRDE! Fel. De icke -numeriska tecknen är borta nu.
Nästa IFERROR -funktion ersätter alla #VALUE -fel med "" (tomt). Nu sitter vi kvar med numeriska värden och mellanslag.
Slutligen serveras denna array till TEXTJOIN -funktionen. TEXTJOIN -funktionen sammanfogar dem och vi får strängen som bara innehåller siffror i den.
Förbättra formeln
Ovanstående formel använde ett hårt kodat nummer för att bearbeta antalet tecken (vi tog tjugo). Men du kanske vill att den ska vara dynamisk. I så fall gissade du rätt, vi kommer att använda LEN -funktionen. Det kommer att ta ett exakt antal tecken för bearbetning. Så formeln blir.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jumbled_text, SEQUENCE (LEN(jumbled_text)),1)+0,"")) |
Här kommer LEN -funktionen automatiskt att detektera de exakta siffertecknen i den alfanumeriska strängen. Detta kommer att lindra bördan att bestämma det maximala antalet tecken.
Alternativ till SEQUENCE -funktion
Om du inte vill använda SEQUENCE -funktionen kan du använda en kombination av ROW och INDIRECT -funktionen för att generera sekventiella nummer.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jumbled_text,RAD(INDIREKT("1:"&LEN(jumbled_text))),1)+0,"")) |
INDIRECT kommer att konvertera texten ("1:20") till det faktiska intervallet och sedan visar RAD -funktionen alla radnummer från 1 till 20. (20 är bara till exempel. Det kan vara valfritt tal).
Så ja killar, så här kan du rippa bort de icke -numeriska tecknen från en alfanumerisk sträng i excel. Jag hoppas att jag var tillräckligt förklarande och att den här artikeln hjälpte dig. Om du har några frågor angående detta ämne eller något annat Excel/VBA -ämne. Tills dess fortsätter Excelling.
Dela nummer och text från sträng i Excel 2016 och äldre: När vi inte hade TEXTJOIN -funktionen använde vi VÄNSTER- och HÖGER -funktioner med några andra funktioner för att dela numeriska och icke -numeriska tecken från en sträng.
Extrahera text från en sträng i Excel med Excel -funktionen VÄNSTER och HÖGER: För att ta bort text i excel från strängen kan vi använda excels VÄNSTER- och HÖGER -funktion. Dessa funktioner hjälper oss att hugga strängar dynamiskt.
Ta bort ledande och bakre mellanslag från text i Excel: Ledande och bakre utrymmen är svåra att känna igen visuellt och kan förstöra din data. Att ta bort dessa tecken från strängen är en grundläggande och viktigaste uppgift vid datarensning. Så här gör du det enkelt i Excel.
Ta bort tecken från höger: För att ta bort tecken från höger om en sträng i Excel använder vi VÄNSTER -funktionen. Ja, VÄNSTER -funktionen. Funktionen VÄNSTER behåller det angivna antalet tecken från VÄNSTER och tar bort allt från höger.
Ta bort oönskade tecken i Excel: För att ta bort oönskade tecken från en sträng i Excel använder vi funktionen SUBSTITUTE. SUBSTITUTE -funktionen ersätter de givna tecknen med ett annat givet tecken och producerar en ny ändrad sträng.
Så här tar du bort text i Excel från en position i Excel: För att ta bort text från en startposition i en sträng använder vi funktionen REPLACE i Excel. Denna funktion hjälper oss att bestämma startpositionen och antalet tecken som ska strippas.
Populära artiklar:
50 Excel -genvägar för att öka din produktivitet | Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.
Hur man använder Excel VLOOKUP -funktion| Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
Hur man använder Excel COUNTIF -funktion| Räkna värden med förhållanden med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.
Hur man använder SUMIF -funktionen i Excel | Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.