Tag Archives: len

POIMI.TEKSTI (MID) -funktio

POIMI.TEKSTI funktiolla voit poimia merkkijonon keskeltä halutun määrän merkkejä kuten esimerkiksi syntymävuoden hetusta.
Esimerkki 1:
jos hetu on solussa A1 ja muotoa 241290-123A niin saat vuoden seuraavasti: =POIMI.TEKSTI(A1;5;2)
Funktion syntaksi on merkkijono;aloituskohta;merkkien määrä.
Esimerkki 2:
Paikkakunnan poiminta solusta jossa on sekä postinumero että paikkakunta.
Solussa A1 on 1600 Vantaa niin paikkakunnan saat seuraavalla kaavalla: =POIMI.TEKSTI(A1;7;PITUUS(A1)-6)
Kaavassa käytetty PITUUS (LEN) funktiota joka laskee merkkien määrän (12) miinustaen pituudesta postinumeron ja yhden välilyönnin merkkimäärän (6).

Puuttuvat etunollat

Jäivätkö etunollat pois kun toit tiedot Exceliin?
Ne saadaan takaisin erittäin kätevästi parilla funktiolla, jos tieto on määrämittaista.

Otetaan esimerkiksi postinumerot.
Excelissä on A-sarakkeessa seuraavanlaiset tiedot (etunollat puuttuvat) ja tiedon pituus pitää olla 5-merkkiä:
100
2200
16100

Rivillä 1 puuttuu 2 nollaa, rivillä 2 puuttuu 1 nolla ja rivi 3 on oikeanpituinen.

Monet ratkaisevat tämän sisäkkäisillä JOF (IF) lausekkeilla. Se voidaan tehdä yksinkertaisemmin TOISTA (REPT) ja PITUUS (LEN) funktioilla.

Kaava, jonka rakennan B-sarakkeeseen, on seuraavanlainen:

=TOISTA(“0”;5-PITUUS(A1))&A1
=REPT(“0”;5-LEN(A1))&A1

Eli: TOISTA-funktio toistaa tekstin “0” niin monta kertaa kuin se puuttuu merkkijonosta A1:ssä.
Tämä toistojen määrä lasketaan kaavalla 5-PITUUS(A1).
5 on merkkijonon maksimipituus. A1:n merkkimäärä saadaan funktiolla PITUUS(A1).
Näiden erotus kertoo, kuinka monta nollaa laitetaan lisää.
Kaikki tämä yhdistetään olemassa olevaan arvoon solussa A1 funktiolla &. Tämä “et” merkki on toiminnoltaan sama kun funktio KETJUTA (CONCATENATE), mutta helpompi ja nopeampi kirjoittaa kuin varsinainen funktio.

Lopputulos on B-sarakkeessa:
00100
02200
16100