Preuzmite primjer radne knjige
Ovaj vodič će pokazati vam kako pronaći i izdvojiti broj iz tekstualnog niza u Excelu i Google tablicama.
Pronađi i izvuci broj iz niza
Ponekad vaši podaci mogu sadržavati brojeve i tekst pa želite izdvojiti numeričke podatke.
Ako je brojčani dio s desne ili lijeve strane niza, relativno je lako podijeliti broj i tekst. Međutim, ako su brojevi unutar niza, tj. Između dva tekstualna niza, morat ćete koristiti mnogo složeniju formulu (prikazano u nastavku).
TEXTJOIN - Izdvojite brojeve u Excelu 2016+
U Excelu 2016 uvedena je funkcija TEXTJOIN koja može izdvojiti brojeve s bilo kojeg mjesta u tekstu. Evo formule:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1)*1), ""))) |
Pogledajmo kako funkcionira ova formula.
Funkcije ROW, INDIRECT i LEN vraćaju niz brojeva koji odgovaraju svakom znaku u vašem alfanumeričkom nizu. U našem slučaju, "Monday01Day" ima 11 znakova, pa će funkcija ROW tada vratiti niz {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}.
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}, 1)*1), ""))) |
Zatim funkcija MID izdvaja svaki znak iz tekstualnog niza, stvarajući niz koji sadrži vaš izvorni niz:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "}*1)," ")) |
Pomnoženje svake vrijednosti u nizu s 1 stvorit će niz koji sadrži pogreške Ako je znak polja tekst:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!; 0; 1; #VALUE!;#VALUE!;#VALUE! !}), ""))) |
Zatim funkcija IFERROR uklanja vrijednosti pogrešaka:
| 1 | = TEXTJOIN ("", TRUE, {""; ""; ""; ""; ""; ""; 0; 1; ""; ""; ""}) |
Ostavljajući samo funkciju TEXTJOIN koja spaja preostale brojeve zajedno.
Imajte na umu da će vam formula dati sve numeričke znakove zajedno iz niza. Na primjer, ako je vaš alfanumerički niz Monday01Day01, rezultat će biti 0101.
Izdvoji brojeve - prije Excela 2016
Prije Excela 2016, mogli biste koristiti mnogo složeniju metodu za izdvajanje brojeva iz teksta. Funkciju FIND možete koristiti zajedno s funkcijama IFERROR i MIN za određivanje prve pozicije numeričkog dijela i prve pozicije tekstualnog dijela nakon broja. Zatim jednostavno izdvojimo brojčani dio s funkcijom MID.
| 1 | = MID (B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999)), MIN (IFERROR (SEARCH ({"a" , "b", "c", "d", "e", "f", "g", "h", "I", "j", "k", "l", "m", " n "," o "," p "," q "," r "," s "," t "," u "," v "," w "," x "," y "," z " }, B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999))), 999999999))-MIN (IFERROR (FIND (FIND ( {0,1,2,3,4,5,6,7,8,9}, B3), 999999999))) |
Napomena: Ovo je formula niza, morate unijeti formulu pritiskom na CTRL + SHIFT + ENTER, umjesto samo ENTER.
Pogledajmo korak po korak kako ova formula radi.
Pronađi broj šake
Pomoću funkcije FIND možemo locirati početnu poziciju broja.
| 1 | = MIN (IFERROR (PRONAĐI ({1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
Za argument find_text funkcije FIND koristimo konstantu niza {0,1,2,3,4,5,6,7,8,9}, zbog čega funkcija FIND izvodi zasebno pretraživanje svake vrijednosti u konstanta niza.
Argument Within_text funkcije FIND u našem slučaju je Monday01Day, u kojem se 1 može pronaći na položaju 8, a 0 na položaju 7, pa će naš niz rezultata biti: {8,#VALUE,#VALUE,#VALUE, #VALUE, #VALUE, #VALUE, #VALUE, #VALUE, 7}.
Pomoću funkcije IFERROR pogreške #VALUE zamjenjujemo s 999999999. Zatim jednostavno tražimo minimum unutar ovog niza i stoga dobivamo mjesto prvog broja (7).
Imajte na umu da je gornja formula formula niza, morate pritisnuti Ctrl+Shift+Enter da biste je pokrenuli.
Pronađi prvi tekstualni znak nakon broja
Slično pronalaženju prvog broja unutar niza, pomoću funkcije FIND utvrđujemo gdje tekst počinje ponovno nakon broja koji dobro koristi i argument start_num funkcije.
| 1 | = MIN (IFERROR (FIND ({"a", "b", "c", "d", "e", "f", "g", "h", "I", "j", "k "," l "," m "," n "," o "," p "," q "," r "," s "," t "," u "," v "," w ", "x", "y", "z"}, B3, C3), 999999999)) |
Ova formula radi vrlo slično prethodnoj koja se koristi za pronalaženje prvog broja, samo što mi koristimo slova u konstanti niza pa brojevi uzrokuju #VALUE pogreške. Imajte na umu da počinjemo s pretraživanjem tek nakon pozicije određene za prvi broj (to će biti argument start_num), a ne od početka niza.
Ne zaboravite pritisnuti Ctrl+Shift+Enter da biste koristili gornju formulu.
Ne preostaje ništa drugo nego sve ovo spojiti.
Izdvoji broj iz dva teksta
Nakon što imamo početnu poziciju brojčanog dijela i početak tekstualnog dijela nakon toga, jednostavno pomoću funkcije MID izdvajamo željeni brojčani dio.
| 1 | = MID (B3, C3, D3-C3) |
Druga metoda
Bez detaljnijeg objašnjenja, također možete upotrijebiti donju složenu formulu za dobivanje broja iz niza.
| 1 | = SUMPRODUCT (MID (0 & B3, LARGE (INDEX (ISNUMBER (-MID (B3, ROW (INDIRECT) ("1:" & LEN (B3)))), 1) ROW (INDIRECT ("1:" & LEN (B3) )), 0), ROW (INDIRECT ("1:" & LEN (B3))))+1,1)*10^ROW (INDIRECT ("1:" & LEN (B3)))/10) |
Imajte na umu da će vam ova gornja formula dati 0 ako nema broja u nizu i da će vodeće nule biti izostavljene.
Pronađite i izdvojite broj iz niza u tekst u Google tablicama
Gore prikazani primjeri rade na isti način u Google tablicama kao i u Excelu.
