Yleinen kaava
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),A1&"0123456789"))
Yhteenveto
Voit erottaa tekstin ja numerot käyttämällä kaavaa, joka perustuu HAKU-funktioon, MIN-funktioon ja LEN-toimintoon VASEN- tai OIKEA-funktiolla riippuen siitä, haluatko purkaa tekstin vai numeron. Esitetyssä esimerkissä kaava kohdassa C5 on:
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),B5&"0123456789"))
joka palauttaa arvon 7, numeron 3 sijainnin merkkijonossa "omenat30".
Selitys
Yleiskatsaus
Kaava näyttää monimutkaiselta, mutta mekaniikka on itse asiassa melko yksinkertainen.
Kuten useimmissa kaavoissa, jotka jakavat tai purkavat tekstiä, avain on etsiä etsimäsi asian sijainti. Kun sinulla on sijainti, voit käyttää muita toimintoja poimimaan tarvitsemasi.
Tässä tapauksessa oletetaan, että numerot ja teksti ovat yhdistettyjä ja että numero näkyy tekstin jälkeen. Yhdessä solussa näkyvästä alkuperäisestä tekstistä haluat jakaa tekstin ja numerot erillisiin soluihin seuraavasti:
| Alkuperäinen | Teksti | Määrä |
| Omenat30 | Omenat | 30 |
| persikat24 | persikat | 24 |
| appelsiinit 12 | appelsiinit | 12 |
| persikat 0 | persikat | 0 |
Kuten edellä todettiin, avain tässä tapauksessa on paikantaa numeron alkupiste, jonka voit tehdä seuraavalla kaavalla:
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),A1&"0123456789"))
Kun sinulla on sijainti, pura vain teksti käyttämällä:
=LEFT(A1,position-1)
Ja poimia vain numero, käytä:
=RIGHT(A1,LEN(A1)-position+1)
Ensimmäisessä yllä olevassa kaavassa etsimme numeron alkuaseman FIND-funktiolla. Löydötekstissä käytämme matriisivakiota (0,1,2,3,4,5,6,7,8,9), tämä saa FIND-funktion suorittamaan erillisen haun kullekin matriisivakion arvolle. Koska matriisivakio sisältää 10 numeroa, tuloksena on taulukko, jolla on 10 arvoa. Esimerkiksi, jos alkuperäinen teksti on "omenat30", tuloksena oleva taulukko on:
(8,10,11,7,13,14,15,16,17,18)
Tämän taulukon kukin numero edustaa kohteen sijaintia matriisivakiossa alkuperäisen tekstin sisällä.
Seuraavaksi MIN-funktio palauttaa luettelon pienimmän arvon, joka vastaa alkuperäisen tekstin ensimmäisen numeron sijaintia. Pohjimmiltaan FIND-funktio saa kaikki numeropaikat, ja MIN antaa meille ensimmäisen numeron sijainnin: huomaa, että 7 on matriisin pienin arvo, joka vastaa luvun 3 sijaintia alkuperäisessä tekstissä.
Saatat miettiä, mikä on outo sisäinen_teksti -rakenne hakutoiminnossa:
B5&"0123456789"
Tämä kaavan osa yhdistää kaikki mahdolliset numerot 0-9 alkuperäiseen tekstiin kohdassa B5. Valitettavasti FIND ei palauta nollaa, kun arvoa ei löydy, joten tämä on vain fiksu tapa välttää virheitä, joita saattaa esiintyä, kun numeroa ei löydy.
Koska oletamme, että tässä esimerkissä luku näkyy aina toisena alkuperäisessä tekstissä, se toimii hyvin, koska MIN pakottaa palauttamaan vain pienimmän tai ensimmäisen esiintymän numerosta. Niin kauan kuin numero näkyy alkuperäisessä tekstissä, kyseinen sijainti palautetaan.
Jos alkuperäinen teksti ei sisällä numeroita, palautetaan "väärä" sijainti, joka on yhtä suuri kuin alkuperäinen teksti + 1. Tässä väärässä sijainnissa yllä oleva VASEN kaava palauttaa edelleen tekstin ja OIKEA kaava palauttaa tyhjän merkkijonon ("").
