Moi. Minulla on jostakin tietokannasta kerätty data työkirjassa ( excel 2013 versio kait ), jossa on lähes 20 000 riviä. Yhdessä solussa pitäisi olla hinta euroina, mutta nyt on tullut tietoja ottaessa kai virhe, että siinä näkyy hinnan lisäksi noin 10 pilkkua luvun lopussa. Lisäksi hinnan pituus vaihtelee, eli toisen rivin arvo voi olla vaikka 1,1 ja seuraavan 1234,56. Nyt pitäisi saada tuolta lopusta siivottua ylimääräiset pilkut pois jotta hintoja voi käsitellä taulukossa. Haasteet:
- pilkkujen määrä hinnan perässä vaihtelee 7 ja 10 välissä
- luvun kokonaislukuosan pituus vaihtelee myös 1 ja 10 tuhannen välissä.
- jos luku on ollut kokonaisluku, siihen ei ole tallennettu desimaalinollaa vaan esim alkuperäinen 33,00 on nyt 33,,,,,,,,, . Vastaavasti taas alkuperäinen 33,13 on nyt 33,13,,,, .
Keksiikö kukaan funktioita miten tuon saisi siivottua? Esim 5 ensimmäisen merkin poiminta rivin alustahan ei tuo oikeaa tulosta kun tallennetun luvun pituus vaihtelee... etsi/korvaa siten että pilkku korvataan tyhjäksi sotkee desimaalikäsittelyn yms... lopusta päin tietty määrä merkkejä poistaenkin lopputulos ei satu joka rivillä oikein.
Rautalankaesimerkki arvoista joita siellä esiintyy:
1205,01,,,,,,,,
3,,,,,,,,,,,,
4,75,,,,,,
4,7,,,,,,,,,
123,8,,,,,,,,
jne. ideoita kenelläkään?
-- Janne
- pilkkujen määrä hinnan perässä vaihtelee 7 ja 10 välissä
- luvun kokonaislukuosan pituus vaihtelee myös 1 ja 10 tuhannen välissä.
- jos luku on ollut kokonaisluku, siihen ei ole tallennettu desimaalinollaa vaan esim alkuperäinen 33,00 on nyt 33,,,,,,,,, . Vastaavasti taas alkuperäinen 33,13 on nyt 33,13,,,, .
Keksiikö kukaan funktioita miten tuon saisi siivottua? Esim 5 ensimmäisen merkin poiminta rivin alustahan ei tuo oikeaa tulosta kun tallennetun luvun pituus vaihtelee... etsi/korvaa siten että pilkku korvataan tyhjäksi sotkee desimaalikäsittelyn yms... lopusta päin tietty määrä merkkejä poistaenkin lopputulos ei satu joka rivillä oikein.
Rautalankaesimerkki arvoista joita siellä esiintyy:
1205,01,,,,,,,,
3,,,,,,,,,,,,
4,75,,,,,,
4,7,,,,,,,,,
123,8,,,,,,,,
jne. ideoita kenelläkään?
-- Janne
Vastaukset 4
Vanhimmat
Nyt ei ole käytössä Exceliä, joten rustasin kokeilun Libren Calcilla, mutta vastaava pitäisi onnistua Excelilläkin.
Vaihtoehto 1 funktioiden avulla:
Tässä oletetaan, että tekstimuodossa olevat arvot ovat A-sarakkeessa, ja ensimmäinen arvo on solussa A1. Tehdään B,C,D ja E-sarakkeisiin apufunktiot, ja F-sarakkeeseen tulee lopullinen numeerinen arvo. Pilkkuja pitää olla myös vähintään kaksi, koska virheentarkistuksia ei ole.
Etsitään ensimmäisen pilkun paikka:
B1: =SEARCH(",";A1;1)
Etsitään seuraavan pilkun paikka:
C1: =SEARCH(",";A1;B1 1)
Muutetaan kokonaislukuosuus luvuksi:
D1: =VALUE(MID(A1;1;B1))
Muutetaan mahdollinen desimaaliosuus luvuksi:
E1: =IF(C1-1>B1;VALUE(MID(A1;B1;C1-B1));0)
Summataan luvut lopulliseksi luvuksi:
F1: =D1 E1
Antamillasi luvuilla pitäisi soluihin tulla seuraavat arvot:
A B C D E F
1205,01,,,,,,,, 5 8 1205 0,01 1205,01
3,,,,,,,,,,,, 2 3 3 0 3
4,75,,,,,, 2 5 4 0,75 4,75
4,7,,,,,,,,, 2 4 4 0,7 4,7
123,8,,,,,,,, 4 6 123 0,8 123,8
En kirjoittanutkaan vaihtoehto kakkosta, joka olisi ollut "teksti sarakkeisiin"-toiminnon avulla. Sillä saa myös nopeasti kokonaisluvut ja desimaaliosat eroteltua, kun laittaa erottelijaksi pilkun. Pitää vaan muistaa muuttaa sarakkeet ensin tekstimuotoon, jotta desimaaliosan etunollat säilyvät. Tietysti jos oletetaan desimaaliosan olevan pelkkiä sadasosia, niin silloin ei tarvitse sitäkään tehdä. Laskee vaan lopuksi yhteen kokonaisluvun ja desimaaliosan luvun jaettuna sadalla.
=LEFT(A1;SEARCH(",,";A1)-1)
Keep EXCELing
@kunde
Kiitos! tämä toimi juuri niinkuin piti ja mukavan yksinkertaisesti. Loistavaa!
-- Janne