Prenesite primer delovnega zvezka
Ta vadnica bo vam pokaže, kako poiščete in izvlečete številko iz besedilnega niza v Excelu in Google Preglednicah.
Poiščite in izvlecite številko iz niza
Včasih lahko vaši podatki vsebujejo številke in besedilo, zato želite izvleči številske podatke.
Če je številčni del na desni ali levi strani niza, je razmeroma enostavno razdeliti številko in besedilo. Če pa so številke znotraj niza, to je med dvema nizoma besedila, boste morali uporabiti veliko bolj zapleteno formulo (prikazano spodaj).
TEXTJOIN - Izvlecite številke v Excelu 2016+
V Excelu 2016 je bila uvedena funkcija TEXTJOIN, ki lahko izvleče številke kjer koli v besedilnem nizu. Tukaj je formula:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1)*1), ""))) |
Poglejmo, kako deluje ta formula.
Funkcije ROW, INDIRECT in LEN vrnejo niz številk, ki ustrezajo vsakemu znaku v vašem alfanumeričnem nizu. V našem primeru ima "Monday01Day" 11 znakov, zato bo funkcija ROW vrnila matriko {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), ""))) |
Nato funkcija MID izvleče vsak znak iz besedilnega niza in ustvari matriko, ki vsebuje vaš izvirni niz:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a" ";" y "}*1)," ")) |
Če pomnožite vsako vrednost v matriki z 1, boste ustvarili niz, ki vsebuje napake, če je bil znak polja besedilo:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!; 0; 1; #VALUE!;#VALUE!;#VALUE!) !}), ""))) |
Nato funkcija IFERROR odstrani vrednosti napak:
| 1 | = TEXTJOIN ("", TRUE, {""; ""; ""; ""; ""; ""; 0; 1; ""; ""; ""}) |
Pustite samo funkcijo TEXTJOIN, ki združi preostale številke.
Upoštevajte, da vam bo formula dala vse številske znake skupaj iz niza. Na primer, če je vaš alfanumerični niz Monday01Day01, vam bo posledično dal 0101.
Izvleči številke - pred Excelom 2016
Pred Excelom 2016 bi lahko za izločanje številk iz besedila uporabili veliko bolj zapleteno metodo. Funkcijo FIND lahko uporabite skupaj s funkcijama IFERROR in MIN, da določite tako prvo mesto številskega dela kot prvo mesto besedilnega dela za številko. Nato preprosto izvlečemo številčni del s funkcijo MID.
| 1 | = MID (B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999)), MIN (IFERROR (ISKANJE ({"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 (NAPAKA (NAJDI ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999))), 999999999))-MIN (NAPAKA (NAJDI ( {0,1,2,3,4,5,6,7,8,9}, B3), 999999999))) |
Opomba: To je formula matrike, formulo morate vnesti s pritiskom na CTRL + SHIFT + ENTER, namesto samo ENTER.
Poglejmo korak za korakom, kako deluje ta formula.
Poiščite številko pest
Za iskanje začetnega položaja številke lahko uporabimo funkcijo FIND.
| 1 | = MIN (IFERROR (NAJDI ({1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
Za argument find_text funkcije FIND uporabljamo konstanto matrike {0,1,2,3,4,5,6,7,8,9}, zaradi katere funkcija FIND izvede ločeno iskanje za vsako vrednost v konstanta matrike.
Argument Within_text funkcije FIND je v našem primeru Monday01Day, v katerem je 1 na položaju 8 in 0 na položaju 7, zato bo naša matrika rezultatov: {8,#VALUE,#VALUE,#VALUE, #VALUE, #VALUE, #VALUE, #VALUE, #VALUE, 7}.
S funkcijo IFERROR napake #VALUE nadomestimo z 999999999. Nato preprosto iščemo minimum v tem nizu in tako dobimo mesto prve številke (7).
Upoštevajte, da je zgornja formula matrična formula, za pritisk morate pritisniti Ctrl+Shift+Enter.
Poišči prvi besedilni znak za številko
Podobno kot pri iskanju prve številke v nizu s funkcijo FIND določimo, kje se besedilo začne znova po številki, pri čemer je dobro uporabljen tudi argument start_num funkcije.
| 1 | = MIN (NAPAKA (NAJDI ({"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)) |
Ta formula deluje zelo podobno prejšnji, ki se uporablja za iskanje prve številke, le da v konstanti matrike uporabljamo črke, zato številke povzročajo napake #VALUE. Upoštevajte, da začnemo iskati šele po položaju, določenem za prvo številko (to bo argument start_num), in ne od začetka niza.
Za uporabo zgornje formule ne pozabite pritisniti Ctrl+Shift+Enter.
Nič drugega ne preostane kot združiti vse skupaj.
Izvleči številko iz dveh besedil
Ko imamo začetni položaj številskega dela in začetek besedilnega dela za tem, preprosto uporabimo funkcijo MID za izvlečenje želenega številskega dela.
| 1 | = MID (B3, C3, D3-C3) |
Druga metoda
Ne da bi to podrobneje pojasnili, lahko uporabite tudi spodnjo kompleksno formulo, da dobite številko znotraj 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) |
Upoštevajte, da vam bo zgornja formula dala 0, če v nizu ni nobene številke in da bodo začetne ničle izpuščene.
Poiščite in izvlecite številko iz niza v besedilo v Google Preglednicah
Zgornji primeri delujejo na enak način v Google Preglednicah kot v Excelu.
