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 i spelet med nya leksaker, jag menar funktioner.
Excel 2019 och 365 introducerar några nya funktioner (TEXTJOIN och SEQUENCE) som kan underlätta uppgiften att ta bort numeriska tecken och hämta endast icke -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,OM(FEL(MITTEN(jumbled_text,SEKVENS(NumChars), 1) +0),MITTEN(jumbled_text,SEKVENS(NumChars), 1), "")))) |
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 numeriska tecken och extrahera alla alfabet och icke -numeriska tecken.
Så här har vi lite alfanumerisk text. Denna text innehåller några siffror och några icke -numeriska tecken. Jag behöver bli av med numeriska tecken och bara få alfabet och andra teckenvärden i kolumnen D.
Jag förväntar mig inte att det totala antalet tecken i blandad text ska vara mer än 100. Så värdet på NumChars är 100 här. Du kan öka eller minska detta antal om du behöver.
Använd ovanstående generella formel här för att ta bort de numeriska tecknen.
| =TEXTJOIN("",SANN,OM(FEL(MITTEN(B3,SEKVENS(100),1)+0),MITTEN(B3,SEKVENS(100),1),""))) |
När du trycker på enter -knappen får du bort alla siffertecken och bara alfabetiska värden återstår. Dra ner denna formel för att ta bort siffror från strängen från alla celler i kolumn B.
Hur fungerar det?
Låt oss först se hur denna formel löses steg för steg.
| 1-> TEXTJOIN("",SANN,OM(FEL(MITTEN(B3,SEKVENS(100),1)+0),MITTEN(B3,SEKVENS(100),1),""))) |
| 2-> TEXTJOIN("",SANN,OM(FEL(MITTEN("This1 is … site",{1,2,3,… 100},1)+0),MITTEN(B3,SEKVENS(100),1),""))) |
| 3-> TEXTJOIN("",SANN,OM(FEL({"T"; "h"; "i"; "s"; "", "1" … ""; ""}+0),MITTEN(B3,SEKVENS(100),1),""))) |
| 4-> TEXTJOIN("",SANN,OM(FEL({#VÄRDE!;#VÄRDE!;#VÄRDE!;#VÄRDE!; 1 …;#VÄRDE!}),MITTEN(B3,SEKVENS(100),1),""))) |
| 5-> TEXTJOIN("",SANN,OM({TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE},MITTEN(B3,SEKVENS(100),1),""))) |
| 6-> TEXTJOIN("",SANN,{"T"; "h"; "i"; "s"; ""…. ""}}) |
| 7-> "Detta är webbplats nummer ett" |
För enkel läsning har jag inte skrivit hela matrisen. Jag använde punkter (…) för att indikera långa matriser.
Som du kan se börjar formeln lösa inifrån. Först är SEQUENCE -funktionen löst. Eftersom vi har passerat 100 som antalet tecken. Den returnerar en rad nummer som börjar från 1 till 100.
Denna matris serveras till MID -funktionen som startnummer. Mittfunktionen går till varje index i sträng och delar varje tecken i en array. Du kan se det i steg 3. Jag har inte visat hela arrayen eftersom det kommer att ta för mycket plats. {"T"; "h"; "i"; "s"; "", "1" … ""; ""}
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. {#VÄRDE!;#VÄRDE!;#VÄRDE!;#VÄRDE!; 1 …;#VÄRDE!}
Denna matris serveras till FEL fungera. Som du vet returnerar ISERROR -funktionen TRUE för fel och FALSE för icke -felvärden. Därför får vi en uppsättning av SANT och FALSKT. TRUE för icke -numeriska tecken och FALSE av siffror. {TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE}.
Samma sak händer med TRUE sektionsuttalanden i IF (MITTEN(B3,SEKVENS(100),1)). Den returnerar en uppsättning med alla tecken i alfanumerisk sträng i B3.
Nu för varje SANT -sats returnerar IF -funktionen motsvarande tecken från matrisen i det sanna avsnittet. För FALSK returnerar IF tomt (""). Nu kommer du att ha en array som inte innehåller något numeriskt tecken. {"T"; "h"; "i"; "s"; ""…. ""}}.
Slutligen serveras denna matris av TEXTJOIN fungera. Denna funktion förenar givna texter med varandra, ignorerar de tomma värdena med en given avgränsare. Därför får vi en sträng som inte innehåller några numeriska tecken. Detta är webbplatsen nummer ett.
Förbättra formeln
Ovanstående formel använder ett hårdkodat nummer för att bearbeta antalet tecken (vi tog 100). Men du kanske vill att den ska vara dynamisk. I så fall gissade du rätt, du kan använda LEN -funktionen. Det kommer att ta det exakta antalet tecken för bearbetning. Så formeln blir.
| = TEXTJOIN ("", TRUE, IF (ERERROR (MID (jumbled_text, SEQUENCE (LEN(jumbled_text), 1) +0), MIDD (jumbled_text, SEQUENCE (LEN(jumbled_text),1),""))) |
Här kommer LEN -funktionen automatiskt att detektera de exakta siffertecknen i den alfanumeriska strängen. Detta kommer att befria från 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, IF (ERERROR (MID (jumbled_text,RAD(INDIREKT("1:"&LEN(NumChars))), 1) +0), MID (jumbled_text,RAD(INDIREKT("1:"&LEN(NumChars))),1),""))) |
INDIRECT kommer att konvertera texten ("1: 100") till det faktiska intervallet och sedan visar RAD -funktionen alla radnummer från 1 till 100. (100 är bara ett 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.
Så här tar du bort icke -numeriska tecken från celler i Excel 2019: För att ta bort icke -numeriska tecken från en alfanumerisk sträng i Excel använder vi den nya TEXTJOIN -funktionen. Remsa icke -numeriska tecken från en sträng kan hjälpa oss att rensa upp våra data för bättre dataanalys. Så här gör du
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.