Tekstin/numeroiden poiminta solusta

Excel-jumissa

Moi!
Minulla on taulukossa (tod.iso) soluja, joista pitäisi saada lopusta summa talteen eri soluun.

Ongelma on siinä, että lähdesolussa on aika paljon tietoa ennen tuota summaa esim.

"10112 0112 R-KIOSKI /A 4.50 -"

Tuossa on siis paljon välilyöntejä ja desimaalimerkkinä piste sekä miinusmerkki perässä. Tuon saa esim. näin talteen: =POIMI.TEKSTI(A98;ETSI(".";A98)-1;6), jonka tuloksena on "4.50 -", mutta ongelma tulee miten saan isommissa summissa koko summan talteen esim. 432.30 -

Tavoittena olisi saada tuo summa siis omaan soluunsa muodossa "-4,50" tai "-432,30". Pieni lisähaaste on myös siinä, että tuon summan pisteen paikka vaihtelee jonkun verran eri riveillä, että ei voi tehdä kiinteää viittausta.

17

7428

    Vastaukset

    Anonyymi (Kirjaudu / Rekisteröidy)
    5000
    • Tämmöinen

      Function Hinta(s) As Double
         i = Len(s) - 1
         h = ""
         c = Mid(s, i, 1)
         While (c = " " Or c = "." Or c >= 0 And c <= 9)
            h = c & h
            i = i - 1
            c = Mid(s, i, 1)
         Wend
         h = Replace(h, " ", "")
         If Right(s, 1) = "-" Then m = -1 Else m = 1
         Hinta = Val(h) * m
      End Function

    • Excel-jumissa

      Kiitos vastauksesta, mutta en valitettavasti ymmärrä siitä mitään :) Mihin noita koodeja pitäisi laittaa? Sen verran huomasin, että Suomi24 on lyhentänyt tuosta esimerkistäni kaikki ylimääräiset välilyönnit, joita käsiteltävässä aineistossani on vaikka ei sillä välttämättä ole vaikutusta.

      • VBA puolella INSERT/MODUL ja kopioit koodin oikealle valkoiselle alueelle.
        Soluun mihin haluat kaava
        =SummaX(A1), nyt siis solusta A1 haetaan summa

        moduuliin..

        Function SummaX(a As Variant) As Double
        Dim b as VAriant
        b = Split(Replace(StrReverse(Trim(a)), ".", ","), " ")
        Summax = CDbl(StrReverse(b(0)) & StrReverse(b(1)))
        End Function

        Keep EXCELing
        @Kunde


      • Tämmöinen
        kunde kirjoitti:

        VBA puolella INSERT/MODUL ja kopioit koodin oikealle valkoiselle alueelle.
        Soluun mihin haluat kaava
        =SummaX(A1), nyt siis solusta A1 haetaan summa

        moduuliin..

        Function SummaX(a As Variant) As Double
        Dim b as VAriant
        b = Split(Replace(StrReverse(Trim(a)), ".", ","), " ")
        Summax = CDbl(StrReverse(b(0)) & StrReverse(b(1)))
        End Function

        Keep EXCELing
        @Kunde

        Näppärä!
        Oletettavasti käytössä on myös tuhaterottimet (välilyönti). Jos näin, summa katkeaa satasiin.
        Miinus oli luvun perässä, kuten hölmön pankkimatematiikan mukaan tehdään. Jos listassa on myös positiivisia lukuja, tuo ei ymmärrä niitä.


      • kunde
        Tämmöinen kirjoitti:

        Näppärä!
        Oletettavasti käytössä on myös tuhaterottimet (välilyönti). Jos näin, summa katkeaa satasiin.
        Miinus oli luvun perässä, kuten hölmön pankkimatematiikan mukaan tehdään. Jos listassa on myös positiivisia lukuja, tuo ei ymmärrä niitä.

        Oikeassa olet noiden pointtien suhteen, mutta tossa nyt fiksattu versio... ;-)

        Function SummaX2(a As Variant) As Double
        SummaX2 = StrReverse(Val(StrReverse(Replace(a, " ", ""))))
        End Function

        Keep EXCELing
        @Kunde


    • plockare

      Jos luku on aina viimeisten kahden "välilyönnin" välissä, niin etsii niiden kummankin sijainnin ja poimii luvun siitä välistä pois.

    • Excel-jumissa

      Tässä on oikea määrä välilyöntejä merkattuna "-" -merkeillä:

      10112--0112--R-KIOSKI---------------------------/A-------------------------välilyöntejä---------------------------4.50 -

      Miten plockare sellaiset viimeiset kaksi välilyöntiä voisi löytää?

      Sellaista ajattelin, että löytyisikö sellaista kaavaa joka laskisi merkit pisteestä vasempaan suuntaan ensimmäiseen välilyöntiin asti (tässä esimerkissä 1). Pisteestä oikealle löytyykin helposti esim. näin =OIKEA(A98;PITUUS(A98)-KÄY.LÄPI(".";A98;1))

      Jos sellainen kaava löytyisi, niin sitten ketjuttaisi halutut asiat yhteen.

      • plockare

        Minulla ei ole juuri nyt Exceliä käytössä, mutta kokeilin Libre Calcilla poimia näillä oletuksilla:

        - rivillä on vain yksi piste (luvun desimaalierotin)
        - kokonaislukuosuuden edessä on reilusti välilyöntejä
        - kokonaislukuosuus on max 5 merkkiä (voi muuttaa)
        - desimaaliosuus on aina kahden merkin mittainen

        Nämä samat funktiot pitäisi löytyä Excelistäkin, mutta niille pitää etsiä mahdollisesti suomenkieliset vastineet. Tässä tutkittava solu on siis A1.

        =TRIM(MID(A1;FIND(".";A1;1)-5;5)) MID(A1;FIND(".";A1;1) 1;2)/100

        Ylläoleva palauttaa luvun 4,5.

        =TRIM(MID(A1;FIND(".";A1;1)-x;x)) MID(A1;FIND(".";A1;1) 1;2)/100

        Nuo x:t tuossa jälkimmäisessä osoittaa kohdan, johon voi vaihtaa kokonaisulukuosuuden pituuden.

        Eli funktionippu hakee ensin pisteen FIND():lla "." paikan A1:stä. Sen jälkeen MID()-funktiolla leikataan pisteestä alkuun päin x-määrä merkkejä, eli 5 merkkiä tässä tapauksessa. Lopuksi TRIM()-funktiolla siivotaan mahdolliset etunollat pois. Tästä saatiin kokonaislukuosuus, eli 4.

        Sen jälkeen lisätään edelliseen kokonaislukuun vielä desimaalit. Eli vastaavalla tavalla haetaan pisteen jälkeisestä kohdasta eteenpäin 2-merkkiä, jotka ovat 50, ja kun jaetaan vielä 100:lla, niin desimaaliosuus on 0,5. Ja molemmat yhdessä siis 4 0,5=4,5.


      • Tämmöinen

        Kopioi koko roska ja liitä se esim. notepadiin. Muuta (etsi/korvaa) pisteet pilkuiksi jotta Excel ei muuta lukuja päivämääriksi, ja talleta tämä tekstitedostona. Lue sitten tämä tedosto.txt Exceliin valiten kiinteät sarakkeet (tai millä nimellä sitä kutsutaankin) ja laita sarakejako sopiviin paikkoihin. Näin saat kaikki tiedot eriteltynä.


    • Excel-jumissa

      Ok, ihan pätevältä vaikuttaa! Pieni hienosäätö jos löytyisi vielä eli se miten pisteestä vasemmalle saataisi aina ihan oikea määrä merkkejä sen mukaan kuinka pitkä luku on. Eli pitäisi etsiä/hakea/poimia pisteestä vasempaan suuntaan siihen asti, kunnes löytyy välilyönti.

      Jos tätä ei tee vaan ottaa "varman päälle" tarpeeksi pitkän lukujonon, niin tulokseksi tulee ne välilyönnit luvun eteen ja se hankaloittaa tämän (toistaiseksi vielä) tekstimuodossa olevan numeron muuttamista luvuksi.

      • plockare

        Ainakaan Libre Calcissa ei oletusfunktioilla voi hakea solun loppupäästä päin. Siihen tyssäsi se minun ensimmäinen idea välilyöntien hakemisesta. Excelistä en menee takeeseen.

        Äkkiähän tekee oman kustomoidun funktionkin, mutta siinä pitää mennä ehkä "epämukavuusalueelle". Tuossahan taisi jo yksi esimerkki ollakin.

        Ylimääräiset välit eivät ole ongelma, kun käyttää TRIM()-funktiota. Se poistaa tekstimuodossa olevasta merkkijonosta tyhjät alku- ja loppupäästä. Ei niiden kyllä pitäisi haitata "tekstistä luvuksi" muunnosfunktiotakaan.

        Jos tämä on vain kertatyylinen tarve, niin siinä tilanteessa käyttäisin "text to columns" -toimintoa. Ilmeisesti lähdemateriaalisi on tasalevyistä rivin merkkien määrän suhteen, jolloin pilkkominen suoritetaan "fixed width"-tavalla tökkimällä hiirellä jakopaikat haluttuihin kohtiin.


      • Excel-jumissa
        plockare kirjoitti:

        Ainakaan Libre Calcissa ei oletusfunktioilla voi hakea solun loppupäästä päin. Siihen tyssäsi se minun ensimmäinen idea välilyöntien hakemisesta. Excelistä en menee takeeseen.

        Äkkiähän tekee oman kustomoidun funktionkin, mutta siinä pitää mennä ehkä "epämukavuusalueelle". Tuossahan taisi jo yksi esimerkki ollakin.

        Ylimääräiset välit eivät ole ongelma, kun käyttää TRIM()-funktiota. Se poistaa tekstimuodossa olevasta merkkijonosta tyhjät alku- ja loppupäästä. Ei niiden kyllä pitäisi haitata "tekstistä luvuksi" muunnosfunktiotakaan.

        Jos tämä on vain kertatyylinen tarve, niin siinä tilanteessa käyttäisin "text to columns" -toimintoa. Ilmeisesti lähdemateriaalisi on tasalevyistä rivin merkkien määrän suhteen, jolloin pilkkominen suoritetaan "fixed width"-tavalla tökkimällä hiirellä jakopaikat haluttuihin kohtiin.

        Text to columns on vähän vaikea, kun tässä on myös toisella tavalla jaettuja tietoja eli menisi sekaisin niiden kanssa.

        Tuo Trim() saattaisi ratkaista tämän ongelma, jos luvun eteen tulee ylimääräisiä välilyöntejä! Kiitos vinkistä :)


      • plockare
        Excel-jumissa kirjoitti:

        Text to columns on vähän vaikea, kun tässä on myös toisella tavalla jaettuja tietoja eli menisi sekaisin niiden kanssa.

        Tuo Trim() saattaisi ratkaista tämän ongelma, jos luvun eteen tulee ylimääräisiä välilyöntejä! Kiitos vinkistä :)

        Tässä vielä toisenlainen versio, joka palauttaa tekstimuodossa. Tähän pitää laittaa joku maksimipituus koko luvulle (etumerkki muuten huomioitava myös). Tässä se on nyt 10.

        =TRIM(MID(A1;FIND(".";A1) 3-10;10))

        Alla olevat x:t määrittää luvun pituuden.

        =TRIM(MID(A1;FIND(".";A1) 3-x;x))

        Eli haetaan FIND()-funktiolla pisteen paikka ja lisätään siihen 3, jolloin saadaan luvun viimeisen merkin sijainti. Sijainnista vähennetään luvun kokonaispituus, jolloin saadaan alkupään sijainti. Sitten leikataan MID():llä siitä kohtaa eteenpäin pituuden verran, ja lopuksi siivotaan TRIM():llä ylimääräiset välit pois.


    • Pahaksi-meni

      Tämä on tosi paha, jos ei suomi24 sotke kaavaa niin tuo toimii. Kokeilen itse kopioida tuon LibreOfficen taulukkolaskentaan sen jälkeen kun olen sen ensin tuosta tyrkännyt ulos.


      =ARVO(KORVAA(KORVAA(POIMI.TEKSTI(A1;ETSI("-";A1) 11;10);PITUUS(POIMI.TEKSTI(A1;ETSI("-";A1) 11;10))-1;2;);ETSI(".";KORVAA(POIMI.TEKSTI(A1;ETSI("-";A1) 11;10);PITUUS(POIMI.TEKSTI(A1;ETSI("-";A1) 11;10))-1;2;));1;","))

      Linux Mint 18 Sarah
      Xfce 64-bit

      • Pahaksi-meni

        Ei sotkenut suomi24 tuota, ja lopuksi vielä valuutta formaatti soluun niin näyttää hyvältä


      • Pahaksi-meni
        Pahaksi-meni kirjoitti:

        Ei sotkenut suomi24 tuota, ja lopuksi vielä valuutta formaatti soluun niin näyttää hyvältä

        No nythän mie vasta honasin, sinä haluat luvun negatiivisena

        =ARVO(0-KORVAA(KORVAA(POIMI.TEKSTI(A1;ETSI("-";A1) 11;10);PITUUS(POIMI.TEKSTI(A1;ETSI("-";A1) 11;10))-1;2;);ETSI(".";KORVAA(POIMI.TEKSTI(A1;ETSI("-";A1) 11;10);PITUUS(POIMI.TEKSTI(A1;ETSI("-";A1) 11;10))-1;2;));1;","))


    • ="-"& TRIM(SUBSTITUTE(RIGHT(A1;LEN(A1)-FIND("|";SUBSTITUTE(A1;" ";"|";LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))-1)));"-";""))

      ="-"& POISTA.VÄLIT(VAIHDA(OIKEA(A1;PITUUS(A1)-ETSI("|";VAIHDA(A1;" ";"|";PITUUS(A1)-PITUUS(VAIHDA(A1;" ";""))-1)));"-";""))

      Keep EXCELing
      @Kunde

    Ketjusta on poistettu 0 sääntöjenvastaista viestiä.

    Luetuimmat keskustelut

    1. Pienimäki ei anna haastatteluja

      medialle Ähtärin kunnan velka sotkuista, kertovat luotettavat lähteet.
      Ähtäri
      17
      3938
    2. Sinuun ei tehoa mikään

      Annan periksi. En vain jaksa tuulimyllyä vastaan taistella. Miehelle
      Ikävä
      37
      3731
    3. Onko Kuhmolaiset tyytyväisiä?

      Kun pääsivät eroon huijari Polvisesta?
      Kuhmo
      22
      3150
    4. Huomenta ja hyvää tiistaita

      Huomenta ja hyvää tiistaita. ❄️🐺☕❄️
      Ikävä
      88
      1951
    5. En tiedä mitä ajatella

      Petaatko perjantaille jotain. Ei meidän kannata minun sydän on viety. Se joka sen vei ei ole mieheni, etkä se ole sinä.
      Tunteet
      36
      1782
    6. voitaisiinko unohtaa se sotku?

      Olisiko mahdollista aloittaa puhtaalta pöydältä vielä? Ikävä sua ****
      Ikävä
      16
      1669
    7. Hyviä vinkkejä täällä

      Eli kaivattua vain kädestä kiinni ja jos vastustelee niin sanoo, että hei beibi beibi nyt mennään vaan. Näin he elivät e
      Ikävä
      17
      1647
    8. Naisten ulkonäkö, jos miehiä ei olisi?

      Mä meinasin ensiksi, että en aloittaisi tätä keskustelua, ainan nyt, mutta huomasin tossa toisessa ketjussa, että tämä s
      Sinkut
      245
      1609
    9. Myös lähellä keskustaa oleva Marttaliitto jättää lihan pois tilaisuuksistaan ja suosituksistaan.

      Marttaliitossa siirrytään terveellisempään ruokavalioon ja ihmiselle tarpeeton liha jätetään pois, ympäristö ja luonto k
      Suomen Keskusta
      192
      1159
    10. Jee! Palsta toimii jälleen!

      Viestiarkisto näkyy ja kaikki pelittää. Tätä on odotettu, vaikka ei tässä mitään odotettavaa ole ollut.
      Sinkut
      20
      1099
    Aihe