Laadige alla töövihiku näide
See õpetus saab demonstreerige, kuidas Excelis ja Google'i arvutustabelites leida ja ekstraheerida number tekstistringist.
Leidke ja ekstraheerige number stringist
Mõnikord võivad teie andmed sisaldada numbreid ja teksti ning soovite arvandmeid välja võtta.
Kui numbriosa asub stringi paremal või vasakul küljel, on numbri ja teksti jagamine suhteliselt lihtne. Kui aga numbrid on stringi sees, st kahe tekstistringi vahel, peate kasutama palju keerukamat valemit (näidatud allpool).
TEXTJOIN - numbrite ekstrakt Excelis 2016+
Programmis Excel 2016 võeti kasutusele funktsioon TEXTJOIN, mis saab numbrid ekstraheerida tekstistringi kõikjalt. Siin on valem:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1)*1), "")) |
Vaatame, kuidas see valem töötab.
Funktsioonid ROW, INDIRECT ja LEN tagastavad numbrimassiivi, mis vastab teie tähtnumbrilise stringi igale tähemärgile. Meie puhul sisaldab “Monday01Day” 11 tähemärki, seega tagastab funktsioon ROW massiivi {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), "")) |
Seejärel ekstraheerib MID -funktsioon iga märgi tekstistringist, luues massiivi, mis sisaldab teie esialgset stringi:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "}*1)," ")) |
Massiivi iga väärtuse korrutamine 1 -ga loob massiivi, mis sisaldab vigu. Kui massiivi märk oli tekst:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!; 0; 1; #VALUE!;#VALUE!;#VALUE !}), "")) |
Järgmisena eemaldab funktsioon IFERROR veaväärtused:
| 1 | = TEXTJOIN ("", TRUE, {""; ""; ""; "" ";" ";" "; 0; 1;" ";" ";" "}) |
Jättes ainult funktsiooni TEXTJOIN, mis ühendab ülejäänud numbrid.
Pange tähele, et valem annab teile stringist kõik numbrimärgid. Näiteks kui teie tähtnumbriline string on Monday01Day01, annab see tulemuseks 0101.
Väljavõtte numbrid - enne Excel 2016
Enne Excel 2016 võiksite numbrite tekstist väljavõtmiseks kasutada palju keerukamat meetodit. Funktsiooni FIND saate kasutada koos funktsioonidega IFERROR ja MIN, et määrata nii numbriosa esimene asukoht kui ka tekstiosa esimene positsioon pärast numbrit. Seejärel ekstraheerime numbriosa lihtsalt funktsiooniga 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", "mina", "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 ( {0,1,2,3,4,5,6,7,8,9}, B3), 999999999))) |
Märkus. See on massiivi valem, peate sisestama valemi ENTER asemel CTRL + SHIFT + ENTER.
Vaatame samm -sammult, kuidas see valem töötab.
Leia rusika number
Funktsiooni FIND abil saame leida numbri lähtepositsiooni.
| 1 | = MIN (IFERROR (OTSI ({1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
Funktsiooni FIND argumendi find_text jaoks kasutame massiivikonstanti {0,1,2,3,4,5,6,7,8,9}, mis paneb funktsiooni FIND tegema iga väärtuse jaoks eraldi otsingu massiivi konstant.
Funktsiooni FIND argument text_text on meie puhul esmaspäev01Day, kus 1 on positsioonil 8 ja 0 positsioonil 7, seega on meie tulemuste massiiv järgmine: {8,#VALUE,#VALUE,#VALUE, #VALUE, #VALUE, #VALUE, #VALUE, #VALUE, 7}.
Funktsiooni IFERROR abil asendame vead #VALUE väärtusega 999999999. Seejärel otsime sellest massiivist lihtsalt miinimumi ja saame seega esimese numbri koha (7).
Pange tähele, et ülaltoodud valem on massiivivalem, selle käivitamiseks peate vajutama Ctrl+Shift+Enter.
Leia esimene tekstimärk pärast numbrit
Sarnaselt stringi esimese numbri leidmisega kasutame funktsiooni FIND, et määrata, kust tekst pärast numbrit uuesti algab, kasutades ära ka funktsiooni argumenti start_num.
| 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)) |
See valem töötab väga sarnaselt eelmisele, mida kasutati esimese numbri leidmiseks, ainult me kasutame oma massiivikonstandis tähti ja seetõttu põhjustavad numbrid #VALUE vigu. Pange tähele, et me hakkame otsima alles pärast esimese numbri jaoks määratud positsiooni (see on argument start_num) ja mitte stringi algusest.
Ülaltoodud valemi kasutamiseks ärge unustage vajutada Ctrl+Shift+Enter.
Ei jää muud üle, kui see kõik kokku panna.
Väljavõte kahest tekstist
Kui meil on numbriosa alguspositsioon ja pärast seda tekstiosa algus, kasutame lihtsalt funktsiooni MID soovitud numbriosa väljavõtmiseks.
| 1 | = MID (B3, C3, D3-C3) |
Muu meetod
Ilma seda üksikasjalikumalt selgitamata võite kasutada ka allolevat keerulist valemit, et saada number stringi seest.
| 1 | = SUMPRODUCT (MID (0 & B3, LARGE (INDEX (ISNUMBER (-MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))))))), 1))*ROW (INDIRECT ("1:" & LEN (B3)) )), 0), RIDA (KAUDNE ("1:" & LEN (B3))))+1,1)*10^RIDA (KAUDNE ("1:" & LEN (B3)))/10) |
Pidage meeles, et see ülaltoodud valem annab teile 0, kui stringist numbrit ei leita ja et eesmised nullid jäetakse välja.
Leidke ja eraldage Google'i arvutustabelites number stringist teksti
Ülaltoodud näited töötavad Google'i arvutustabelites samamoodi nagu Excelis.
