Atsisiųskite darbaknygės pavyzdį
Ši pamoka bus parodykite, kaip rasti ir išgauti skaičių iš teksto eilutės „Excel“ ir „Google“ skaičiuoklėse.
Raskite ir ištraukite skaičių iš eilutės
Kartais jūsų duomenyse gali būti skaičių ir teksto, o jūs norite išgauti skaitmeninius duomenis.
Jei skaičių dalis yra dešinėje arba kairėje eilutės pusėje, palyginti lengva padalyti skaičių ir tekstą. Tačiau jei skaičiai yra eilutės viduje, t. Y. Tarp dviejų teksto eilučių, turėsite naudoti daug sudėtingesnę formulę (parodyta žemiau).
TEXTJOIN - ištraukite skaičius „Excel 2016+“
„Excel 2016“ buvo įdiegta funkcija TEXTJOIN, kuri gali išgauti skaičius iš bet kurios teksto eilutės. Čia yra formulė:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1)*1), "")) |
Pažiūrėkime, kaip veikia ši formulė.
Funkcijos ROW, INDIRECT ir LEN grąžina skaičių masyvą, atitinkantį kiekvieną jūsų raidžių ir skaitmenų eilutės simbolį. Mūsų atveju „Monday01Day“ turi 11 simbolių, todėl funkcija ROW grąžins masyvą {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), "")) |
Tada MID funkcija išskiria kiekvieną simbolį iš teksto eilutės ir sukuria masyvą, kuriame yra jūsų pradinė eilutė:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "}*1)," ")) |
Padauginus kiekvieną masyvo vertę iš 1, bus sukurtas masyvas su klaidomis. Jei masyvo simbolis buvo tekstas:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!; 0; 1; #VALUE!;#VALUE!;#VALUE !}), "")) |
Tada IFERROR funkcija pašalina klaidų reikšmes:
| 1 | = TEXTJOIN ("", TRUE, {""; ""; ""; ""; ""; ""; 0; 1; ""; ""; ""}) |
Paliekama tik funkcija TEXTJOIN, kuri sujungia likusius skaičius.
Atminkite, kad formulė suteiks visus skaitinius simbolius iš eilutės. Pvz., Jei jūsų raidinė ir skaitmeninė eilutė yra Monday01Day01, ji gaus 0101.
Ištraukite skaičius - prieš „Excel 2016“
Prieš „Excel 2016“ galite naudoti daug sudėtingesnį metodą, kad ištrauktumėte skaičius iš teksto. Galite naudoti FIND funkciją kartu su funkcijomis IFERROR ir MIN, kad nustatytumėte ir pirmąją skaičių dalies padėtį, ir pirmąją teksto dalies vietą po skaičiaus. Tada mes tiesiog ištraukiame skaičių dalį naudodami MID funkciją.
| 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“, „aš“, „j“, „k“, „l“, „m“, „ n "," o "," p "," q "," r "," s "," t "," u "," v "," w "," x "," y "," z " }, B3, MIN (IFERROR (RASTI ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999))), 999999999))-MIN (IFERROR (FIND ( {0,1,2,3,4,5,6,7,8,9}, B3), 999999999))) |
Pastaba: tai yra masyvo formulė, kurią turite įvesti paspausdami CTRL + SHIFT + ENTER, o ne tik ENTER.
Pažiūrėkime žingsnis po žingsnio, kaip ši formulė veikia.
Raskite kumščio numerį
Mes galime naudoti FIND funkciją, kad surastume skaičiaus pradinę padėtį.
| 1 | = MIN (IFERROR (RASTI ({1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
Funkcijos FIND argumentui find_text naudojame masyvo konstantą {0,1,2,3,4,5,6,7,8,9}, todėl funkcija FIND atlieka atskirą paiešką kiekvienai reikšmei masyvo konstanta.
Funkcijos FIND argumentas „text_text“ mūsų atveju yra „Monday01Day“, kuriame 1 galima rasti 8 pozicijoje, o 0 - 7 pozicijoje, taigi mūsų rezultatų masyvas bus: {8,#VALUE,#VALUE,#VALUE, #VALUE, #VALUE, #VALUE, #VALUE, #VALUE, 7}.
Naudodami funkciją IFERROR, mes pakeičiame #VALUE klaidas 999999999. Tada mes tiesiog ieškome minimumo šiame masyve ir gauname pirmojo skaičiaus vietą (7).
Atminkite, kad aukščiau pateikta formulė yra masyvo formulė, norėdami ją suaktyvinti, turite paspausti Ctrl+Shift+Enter.
Raskite pirmąjį teksto simbolį po skaičiaus
Panašiai kaip ir pirmojo skaičiaus paieška eilutėje, mes naudojame funkciją RASTI, kad nustatytume, kur tekstas vėl prasideda po skaičiaus, taip pat gerai išnaudojant funkcijos argumentą start_num.
| 1 | = MIN (IFERROR (RASTI ({"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)) |
Ši formulė veikia labai panašiai kaip ankstesnė, naudojama pirmam skaičiui surasti, tik mes naudojame masyvo konstantos raides, todėl skaičiai sukelia #VALUE klaidų. Atminkite, kad mes pradedame ieškoti tik po pirmojo skaičiaus nustatytos pozicijos (tai bus argumentas start_num), o ne nuo eilutės pradžios.
Nepamirškite paspausti Ctrl+Shift+Enter, kad galėtumėte naudoti aukščiau pateiktą formulę.
Nieko kito nelieka, kaip viską sujungti.
Ištraukite skaičių iš dviejų tekstų
Kai turėsime skaičiaus dalies pradinę padėtį ir po to teksto dalies pradžią, mes tiesiog naudojame MID funkciją norimai numerio daliai išgauti.
| 1 | = MID (B3, C3, D3-C3) |
Kitas metodas
Nepaaiškindami to išsamiau, taip pat galite naudoti žemiau pateiktą sudėtingą formulę, kad gautumėte skaičių iš eilutės.
| 1 | = SUMPRODUCT (MID (0 & B3, LARGE (INDEX (ISNUMBER (-MID (B3, ROW (INDIRECT ("1:" & LEN (B3))))))))))), 1))*ROW (INDIRECT ("1:" & LEN (B3)) )), 0), EILUTĖ (NETIESIOGINĖ ("1:" & LEN (B3))))+1,1)*10^EILUTĖ (NETIESIOGINĖ ("1:" & LEN (B3)))/10) |
Atminkite, kad ši aukščiau pateikta formulė suteiks jums 0, kai eilutėje nerasite skaičiaus ir kad pirmosios nulio nebus.
Raskite ir ištraukite skaičių iš eilutės į tekstą „Google“ skaičiuoklėse
Aukščiau pateikti pavyzdžiai „Google“ skaičiuoklėse veikia taip pat, kaip „Excel“.
