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 Excel 2019 i 365 u igri s novim igračkama, mislim na funkcije.
Excel 2019 i 365 uvode neke nove funkcije (TEXTJOIN i SEQUENCE) koje mogu olakšati zadatak uklanjanja numeričkih znakova i dohvaćanje samo ne numeričkih vrijednosti u novoj ćeliji. Koristit ćemo formule koje nam u tome mogu pomoći, prikladnije.
Opća formula
| =TEXTJOIN("",PRAVI,AKO(GREŠKA(SREDNJA(zbrkani_tekst,SLIJED(NumChars), 1) +0),SREDNJA(zbrkani_tekst,SLIJED(NumChars), 1), ""))) |
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 numeričke znakove i izdvojite sve abecede i ne numeričke znakove.
Dakle, ovdje imamo neki alfanumerički tekst. Ovaj tekst sadrži neke brojeve i neke ne numeričke znakove. Moram se riješiti numeričkih znakova i dobiti samo abecede i druge vrijednosti znakova, u stupcu D.
Ne očekujem da će ukupan broj znakova u zbrkanom tekstu biti veći od 100. Dakle, vrijednost NumChars je 100 ovdje. Ovaj broj možete povećati ili smanjiti ako je potrebno.
Ovdje primijenite gornju opću formulu da biste uklonili numeričke znakove.
| =TEXTJOIN("",PRAVI,AKO(GREŠKA(SREDNJA(B3,SLIJED(100),1)+0),SREDNJA(B3,SLIJED(100),1),""))) |
Kad pritisnete tipku enter, uklonit će se svi numerički znakovi, a preostale su samo abecedne vrijednosti. Povucite ovu formulu prema dolje da biste uklonili brojeve iz niza iz svih ćelija u stupcu B.
Kako radi?
Prvo da vidimo kako se ova formula rješava korak po korak.
| 1-> TEXTJOIN("",PRAVI,AKO(GREŠKA(SREDNJA(B3,SLIJED(100),1)+0),SREDNJA(B3,SLIJED(100),1),""))) |
| 2-> TEXTJOIN("",PRAVI,AKO(GREŠKA(SREDNJA("Ovo je … web mjesto",{1,2,3,… 100},1)+0),SREDNJA(B3,SLIJED(100),1),""))) |
| 3-> TEXTJOIN("",PRAVI,AKO(GREŠKA({"T"; "h"; "i"; "s"; "", "1"… ""; ""}+0),SREDNJA(B3,SLIJED(100),1),""))) |
| 4-> TEXTJOIN("",PRAVI,AKO(GREŠKA({#VALUE!;#VALUE!;#VALUE!;#VALUE!; 1…; #VREDNOST!}),SREDNJA(B3,SLIJED(100),1),""))) |
| 5-> TEXTJOIN("",PRAVI,AKO({TRUE; TRUE; TRUE; TRUE; FALSE …; TRUE},SREDNJA(B3,SLIJED(100),1),""))) |
| 6-> TEXTJOIN("",PRAVI,{"T"; "h"; "i"; "s"; ""….; ""}}) |
| 7-> "Ovo je web stranica broj jedan" |
Radi lakšeg čitanja nisam napisao cijeli niz. Koristio sam točke (…) za označavanje dugih nizova.
Kao što vidite, formula počinje rješavati iznutra. Isprva je riješena funkcija SEQUENCE. Budući da smo prošli 100 kao broj znakova. Vraća niz brojeva koji počinju od 1 do 100.
Ovaj niz se poslužuje funkciji MID kao početni broj. Srednja funkcija ide do svakog indeksa u nizu i razdvaja svaki znak u nizu. To možete vidjeti u koraku 3. Nisam prikazao cijeli niz jer će zauzeti previše prostora. {"T"; "h"; "i"; "s"; "", "1"… ""; ""}
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. {#VALUE!;#VALUE!;#VALUE!;#VALUE!; 1…; #VREDNOST!}
Ovaj niz se poslužuje u GREŠKA funkcija. Kao što znate, ISERROR funkcija vraća TRUE za pogreške i FALSE za vrijednosti koje nisu pogreške. Stoga dobivamo niz TRUE i FALSE. TRUE za ne -numeričke znakove i FALSE brojeva. {TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE}.
Ista se stvar događa s iskazima TRUE odjeljka IF (SREDNJA(B3,SLIJED(100),1)). Vraća niz svih znakova alfanumeričkog niza u B3.
Sada za svaki izraz TRUE, funkcija IF vraća odgovarajući znak iz niza pravog odjeljka. Za FALSE, IF vraća prazno (""). Sada ćete imati niz koji ne sadrži numeričke znakove. {"T"; "h"; "i"; "s"; ""….; ""}.
Konačno, ovaj niz poslužuje TEXTJOIN funkcija. Ova funkcija međusobno povezuje zadane tekstove, zanemarujući prazne vrijednosti, s zadanim razdjelnikom. Tako dobivamo niz koji ne sadrži numeričke znakove. Ovo je web stranica broj jedan.
Poboljšanje formule
Gornja formula koristi teško kodirani broj za obradu broja znakova (uzeli smo 100). No možda biste htjeli da bude dinamičan. U tom slučaju, dobro ste pogodili, možete koristiti funkciju LEN. Za obradu će biti potreban točan broj znakova. Tako će formula biti.
| = TEXTJOIN ("", TRUE, IF (ISERROR (MID (jumble_text, SEQUENCE)LEN(zbrkani_tekst), 1) +0), MID (zbrkani_tekst, SEQUENCE (LEN(zbrkani_tekst),1),""))) |
Ovdje će funkcija LEN automatski otkriti točan broj znakova u alfanumeričkom nizu. Time ćete se osloboditi tereta određivanja 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, IF (ISERROR (MID (junged_text,RED(IZREDNO("1:"&LEN(NumChars))), 1) +0), MID (zbrkani_tekst,RED(IZREDNO("1:"&LEN(NumChars))),1),""))) |
INDIRECT će tekst ("1: 100") pretvoriti u stvarni raspon, a zatim će funkcija ROW ispisati sve brojeve redaka od 1 do 100. (100 je samo 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.
Kako ukloniti ne numeričke znakove iz ćelija u Excelu 2019: Za uklanjanje ne numeričkih znakova iz alfanumeričkog niza u Excelu koristimo novu funkciju TEXTJOIN. Trak ne numeričkih znakova iz niza može nam pomoći očistiti podatke radi bolje analize podataka. Pa evo kako to radite
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.