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
Vinkkiä solun sisällön siivoukseen
4
139
Vastaukset
- funktiot
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- funktiot
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.
- Kundepuu
=LEFT(A1;SEARCH(",,";A1)-1)
Keep EXCELing
@kunde- HukassaExcelissä
Kiitos! tämä toimi juuri niinkuin piti ja mukavan yksinkertaisesti. Loistavaa!
-- Janne
Ketjusta on poistettu 0 sääntöjenvastaista viestiä.
Luetuimmat keskustelut
Elämä valuu ohi ilman merkitystä
Olen 5-kymppinen korkeasti koulutettu hyvässä ja vaativassa työssä oleva mies. Erosin pitkästä parisuhteesta pari vuotta3235980Martina julkaisi romanttisia kuvia kihlajaisista
Ihana pari. Paljon onnea ja rakkautta heille 💞2761643Ilo, joka nousee silmiisi saakka
kun katseemme kohtaavat. Olet energinen, aito, ihana. Välillä tuijotat suoraan silmiini - enkä hämmenny, katson takaisin661526eerikäinen novassa sanoi ei kukaan enää aja manuaalivaihteilla
meillä on 3 autoa talissa ja kaikissa manuaalilaatikot, on meillä vielä tämmöiset vaikka toisin puhutaan.1411512Gekkosessa hyvä juttu Sofian Dubai "töistä"
"Vielä tammikuussa Belórf lupaili aloittavansa jälleen verkkovalmennukset, mutta tämä projekti näyttää kuihtuneen kaikes1101436- 941355
En oikeasti
Tiennyt että sinulla on ollut vaikeuksia ja huonoja aikoja. Olen oikeasti pahoillani, ja olisin myös toiminut eritavoin1311252Jokaisella on omat syntinsä
Minä olisin niin mielelläni sinun. Ehkä joskus viittasitkin siihen. Olet nainen ajatuksissani jatkuvasti ja taidat tietä611194- 421035
- 172948