Naučili smo kako ukloniti numeričke vrijednosti iz ćelije u Excelu 2016. i starijim. Formule koje smo koristili bile su pomalo složene, ali sada su u igri Excel 2019 i 365.
Excel 2019 i 365 uvode neke nove funkcije koje olakšavaju uklanjanje ne numeričkih znakova i dohvaćanje samo numeričkih vrijednosti u novoj ćeliji. Koristit ćemo formule koje nam u tome mogu pomoći, prikladnije.
Opća formula
| =TEXTJOIN("",PRAVI,POGREŠAK(SREDNJA(zbrkani_tekst,SLIJED(NumChars), 1) +0, ""))) |
Izmijenjeni_tekst: Ovo je izvorni tekst iz kojeg želite izdvojiti sve numeričke vrijednosti.
NumChars: Ovo je ukupan broj znakova koje želite obraditi. Zbrkani_tekst ne smije imati više znakova od ovog broja (znakovi i numerički zajedno).
Pogledajmo primjer kako bismo razjasnili stvari.
Primjer: Uklonite ne numeričke znakove i izvucite sve brojeve
Dakle, imamo neki zbrkani tekst. Ovaj tekst sadrži neke brojeve i neke ne numeričke znakove. Moram se riješiti ne numeričkih znakova i dobiti numeričke vrijednosti samo u stupcu D.
Ne očekujem da će ukupan broj znakova u zbrkanom tekstu biti veći od 20. Dakle, vrijednost NumChars je 20 ovdje. Ovaj broj možete povećati ako trebate.
Ovdje primijenite gornju generičku formulu da biste uklonili ne numeričke znakove.
| =TEXTJOIN("",PRAVI,POGREŠAK(SREDNJA(C3,SLIJED(20),1)+0,"")) |
A kad pritisnete tipku enter. Uklonit ćete sve ne numeričke znakove. Povucite ovu formulu prema dolje da biste uklonili znakove iz niza iz svih ćelija u stupcu C3.
Kako radi?
Prvo da vidimo kako se ova formula rješava korak po korak.
| 1-> TEXTJOIN("",PRAVI,POGREŠAK(SREDNJA(C3,SLIJED(20),1)+0,"")) |
| 2-> TEXTJOIN("",PRAVI,POGREŠAK(SREDNJA("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("",PRAVI,POGREŠAK({"1"; "2"; "a"; "s"; "w"; "1"; "2"; "w"; "1"; "2"; "3"; ""; "" ; ""; ""; ""; ""; ""; ""; ""}}+0,"")) |
| 4-> TEXTJOIN("",PRAVI,POGREŠAK({1; 2; #VALUE!;#VALUE!;#VALUE!; 1; 2; #VALUE!; 1; 2; 3; #VREDNOST!;#VRIJEDNOST!;…; #VREDNOST!}+0,"")) |
| 5-> TEXTJOIN("",PRAVI,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Kao što vidite, formula počinje rješavati iznutra. Isprva je riješena funkcija SEQUENCE. Budući da smo prošli 20. Vraća niz brojeva počevši od 1 do 20.
Ovaj niz se poslužuje funkciji MID kao početni broj. Srednja funkcija ide do svakog indeksa u nizu i razdvaja svaki znak. To možete vidjeti u koraku 3.
Zatim svakom znaku dodajemo 0. U Excelu ako pokušate dodati broj nebrojenim znakovima, rezultat je #VALUE! Pogreška. Tako dobivamo niz brojeva i #VREDNOST! Pogreške. Numerički znakovi sada su nestali.
Sljedeća funkcija IFERROR zamjenjuje sve pogreške #VALUE s "" (prazno). Sada nam preostaju numeričke vrijednosti i razmaci.
Konačno, ovaj niz se poslužuje funkciji TEXTJOIN. Funkcija TEXTJOIN ih povezuje i dobivamo niz koji sadrži samo brojeve.
Poboljšanje formule
Gornja formula koristila je teško kodirani broj za obradu broja znakova (uzeli smo dvadeset). No možda biste htjeli da bude dinamičan. U tom slučaju, pogađate, koristit ćemo funkciju LEN. Za obradu će trebati točan broj znakova. Tako će formula biti.
| = TEXTJOIN ("", TRUE, IFERROR (MID (zbrkani_tekst, SEQUENCE (LEN(zbrkani_tekst)),1)+0,"")) |
Ovdje će funkcija LEN automatski otkriti točan broj znakova u alfanumeričkom nizu. To će rasteretiti određivanje najvećeg broja znakova.
Alternativa funkcije SEQUENCE
Ako ne želite koristiti SEQUENCE funkciju, tada možete koristiti kombinaciju ROW i INDIRECT funkcije za generiranje uzastopnih brojeva.
| = TEXTJOIN ("", TRUE, IFERROR (MID (junged_text,RED(IZREDNO("1:"&LEN(zbrkani_tekst))),1)+0,"")) |
INDIRECT će tekst ("1:20") pretvoriti u stvarni raspon, a zatim će funkcija ROW ispisati sve brojeve redaka od 1 do 20. (20 je samo na primjer. Može biti bilo koji broj).
Pa da, dečki, evo kako možete istrgnuti ne -numeričke znakove iz alfanumeričkog niza u Excelu. Nadam se da sam vam dovoljno objasnio i da vam je ovaj članak pomogao. Ako imate pitanja u vezi ove teme ili bilo koje druge excel/VBA teme. Do tada nastavite s Excellingom.
Podijelite brojeve i tekst iz niza u Excelu 2016 i starijim: Kad nismo imali funkciju TEXTJOIN, koristili smo funkcije LIJEVO i DESNO s nekim drugim funkcijama za odvajanje numeričkih i ne numeričkih znakova iz niza.
Izdvojite tekst iz niza u Excelu pomoću Excelove funkcije LIJEVO I DESNO: Za uklanjanje teksta u excelu iz niza možemo koristiti excelove funkcije LIJEVO i DESNO. Ove nam funkcije pomažu dinamičko sjeckanje nizova.
Uklonite početne i krajnje razmake iz teksta u Excelu: Vodeći i zadnji prostor teško je vizualno prepoznati i mogu poremetiti vaše podatke. Skidanje ovih znakova iz niza osnovni je i najvažniji zadatak u čišćenju podataka. Evo kako to možete učiniti jednostavno u Excelu.
Uklonite znakove s desne strane: Za uklanjanje znakova s desne strane niza u Excelu koristimo funkciju LIJEVO. Da, funkcija LIJEVO. Funkcija LIJEVO zadržava zadani broj znakova iz LIJEVO i uklanja sve s desne strane.
Uklonite neželjene znakove u Excelu: Za uklanjanje neželjenih znakova iz niza u Excelu koristimo funkciju ZAMJENA. Funkcija ZAMJENA zamjenjuje zadane znakove drugim zadanim znakom i proizvodi novi izmijenjeni niz.
Kako ukloniti tekst u Excelu počevši od pozicije u Excelu: Za uklanjanje teksta s početne pozicije u nizu koristimo funkciju ZAMJENA u Excelu. Ova nam funkcija pomaže odrediti početni položaj i broj znakova za skidanje.
Popularni članci:
50 Excel prečaca za povećanje vaše produktivnosti | Budite brži u izvršavanju svojih zadataka. Ovih 50 prečaca učinit će da radite još brže na Excelu.
Kako koristiti Excel VLOOKUP funkciju| Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
Kako koristiti Excel COUNTIF funkcija| Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste brojali određene vrijednosti. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.
Kako koristiti funkciju SUMIF u Excelu | Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.