Lataa esimerkkityökirja
Tämä opetusohjelma tulee näyttää kuinka löytää ja poimia numero tekstimerkkijonosta Excelissä ja Google Sheetsissä.
Etsi ja poimi numero merkkijonosta
Joskus tietosi voivat sisältää numeroita ja tekstiä ja haluat poimia numeeriset tiedot.
Jos numeroosa on merkkijonon oikealla tai vasemmalla puolella, numeron ja tekstin jakaminen on suhteellisen helppoa. Jos numerot ovat merkkijonon sisällä, eli kahden tekstimerkkijonon välissä, sinun on käytettävä paljon monimutkaisempaa kaavaa (esitetty alla).
TEXTJOIN - Pura numeroita Excel 2016+: ssa
Excel 2016: ssa otettiin käyttöön TEXTJOIN -toiminto, joka voi poimia numerot mistä tahansa tekstimerkkijonosta. Tässä on kaava:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((KESKI (B3, RIVI (Epäsuora ("1:" & LEN (B3)))), 1)*1), "")) |
Katsotaan kuinka tämä kaava toimii.
RIVI-, Epäsuora- ja LEN -funktiot palauttavat numerosarjan, joka vastaa aakkosnumeerisen merkkijonosi kutakin merkkiä. Meidän tapauksessamme "Monday01Day" sisältää 11 merkkiä, joten ROW -funktio palauttaa sitten taulukon {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), "")) |
Seuraavaksi MID -toiminto purkaa jokaisen merkin tekstimerkkijonosta ja luo taulukon, joka sisältää alkuperäisen merkkijonon:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "}*1)," ")) |
Jokaisen taulukon arvon kertominen yhdellä luo virheitä sisältävän taulukon, jos taulukon merkki oli teksti:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!; 0; 1; #VALUE!;#VALUE!;#VALUE) !}), "")) |
Seuraavaksi IFERROR -toiminto poistaa virhearvot:
| 1 | = TEXTJOIN ("", TRUE, {""; ""; ""; "" ";" ";" "; 0; 1;" ";" ";" "}) |
Jätetään vain TEXTJOIN -toiminto, joka yhdistää loput numerot yhteen.
Huomaa, että kaava antaa sinulle kaikki numeeriset merkit yhdessä merkkijonostasi. Jos esimerkiksi aakkosnumeerinen merkkijonosi on Monday01Day01, se antaa tulokseksi 0101.
Pura numerot - Ennen Excel 2016: ta
Ennen Excel 2016: ta voit käyttää paljon monimutkaisempaa tapaa poimia numeroita tekstistä. Voit käyttää FIND -funktiota yhdessä IFERROR- ja MIN -toimintojen kanssa määrittääksesi sekä numeroosan ensimmäisen sijainnin että tekstiosan ensimmäisen sijainnin numeron jälkeen. Sitten poimimme vain numero -osan MID -toiminnolla.
| 1 | = MID (B3, MIN (IFERROR (ETSI ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999)), MIN (IFERROR (SEARCH ({"a") , "b", "c", "d", "e", "f", "g", "h", "minä", "j", "k", "l", "m", " n "," o "," p "," q "," r "," s "," t "," u "," v "," w "," x "," y "," z " }, B3, MIN (IFERROR (ETSI ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999))), 999999999))-MIN (IFERROR (ETSI ( {0,1,2,3,4,5,6,7,8,9}, B3), 999999999))) |
Huomautus: Tämä on Array -kaava, sinun on annettava kaava painamalla CTRL + SHIFT + ENTER vain ENTER -näppäimen sijaan.
Katsotaanpa vaihe vaiheelta, miten tämä kaava toimii.
Etsi nyrkkinumero
FIND -toiminnolla voimme etsiä numeron aloituspaikan.
| 1 | = MIN (IFERROR (ETSI ({1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
FIND -funktion find_text -argumentissa käytämme matriisivakioa {0,1,2,3,4,5,6,7,8,9}, joka tekee FIND -funktion suorittamaan erillisen haun jokaiselle taulukon vakio.
FIND -funktion argumentti tekstin sisällä on tapauksessamme maanantai01Päivä, jossa 1 löytyy sijainnista 8 ja 0 sijainnista 7, joten tulosmatriisi on: {8,#VALUE,#VALUE,#VALUE, #VALUE, #VALUE, #VALUE, #VALUE, #VALUE, 7}.
IFERROR -funktiolla korvataan #ARVO -virheet numerolla 999999999. Sitten etsimme yksinkertaisesti minimiä tästä taulukosta ja saamme siis ensimmäisen numeron paikan (7).
Huomaa, että yllä oleva kaava on taulukkokaava, sinun täytyy painaa Ctrl+Vaihto+Enter käynnistääksesi sen.
Etsi ensimmäinen tekstimerkki numeron jälkeen
Samoin kuin merkkijonon ensimmäisen numeron paikantaminen, käytämme FIND -funktiota sen määrittämiseen, missä teksti alkaa uudelleen numeron jälkeen ja hyödyntää myös funktion start_num -argumenttia.
| 1 | = MIN (IFERROR (ETSI ({"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)) |
Tämä kaava toimii hyvin samalla tavalla kuin edellinen, jota käytettiin ensimmäisen numeron paikantamisessa, vain käytämme kirjaimia taulukkovakiossa ja siksi numerot aiheuttavat #VALUE -virheitä. Huomaa, että aloitamme haun vasta ensimmäiselle numerolle määritetyn paikan jälkeen (tämä on argumentti start_num) eikä merkkijonon alusta.
Älä unohda painaa Ctrl+Vaihto+Enter käyttääksesi yllä olevaa kaavaa.
Ei jää muuta kuin kokoamaan tämä kaikki yhteen.
Ote numero kahdesta tekstistä
Kun olemme saaneet lukuosan alkukohdan ja tekstiosan alun sen jälkeen, käytämme yksinkertaisesti MID -toimintoa halutun numeroosan poimimiseksi.
| 1 | = KESKI (B3, C3, D3-C3) |
Muu menetelmä
Selittämättä sitä tarkemmin, voit myös käyttää alla olevaa monimutkaista kaavaa saadaksesi numeron merkkijonon sisältä.
| 1 | = SUMPRODUCT (MID (0 & B3, LARGE (INDEX (ISNUMBER (-MID (B3, ROW (INDIRECT ("1:" & LEN (B3))))))))), 1))*ROW (Epäsuora ("1:" & LEN (B3)) )), 0), RIVI (Epäsuora ("1:" & LEN (B3))))+1,1)*10^RIVI (Epäsuora ("1:" & LEN (B3)))/10) |
Huomaa, että tämä yllä oleva kaava antaa sinulle 0, kun merkkijonosta ei löydy numeroa ja että edessä olevat nollat jätetään pois.
Etsi ja poimi numero Google Sheetsin merkkijonosta tekstiin
Yllä olevat esimerkit toimivat samalla tavalla Google Sheetsissä kuin Excelissä.
