«

»

nov 14

Szöveg szétvágása adott karakter mentén (splitting)

split_2Mutatok nektek egy érdekes formulát, amivel szöveget adott karakter mentén lehet szétdarabolni cellákba – lehet vesszővel, pontosvesszővel elválasztott szöveg, de akár egy mondatot is tudsz szavakra “darabolni”.

Nem lesz nehéz, és minta fájl is van a cikk végén. 😉

 

 

Kezdjük a példával! Az alábbi képen a B oszlopban levő szöveget daraboltam szét a D-G oszlopokba.

split_1

A példában vessző az elválasztó karakter, minden vesszővel elválasztott szövegrész külön cellába került – de lehet ez bármi más is. Nálunk a munkában például tervezési kódokat használunk, amik négy részből állnak, és a részeket aláhúzás választja el. Ilyesmi:

terület_kategória_márka_értékesítési csatorna

Amikor ad-hoc beszámolót, kimutatást készítünk, akkor az alázúzás mentén szétdaraboljuk ezeket a kódokat, hogy könnyen szűrhető, összesíthető kimutatást lehessen rá csinálni: külön oszlopba teszem a területet, a kategóriát, a márkát és a csatornát.

No, de hogyan tudjuk ezt megoldani?

Egy régi trükköt mutatok erre, amit Roberto-tól tanultam. :-)

Egy segéd sorra lesz szükség karakter-pozíció számokkal – ezt szürkével jelöltem.

Így néz ki a formula a D4 cellában:

= KIMETSZ( KÖZÉP( HELYETTE( $B4 ; “,” ; SOKSZOR(” “;100) ) ; D$2 ; 100) )

= TRIM( MID( SUBSTITUTE( $B4 ; “,” ; REPT(” “;100) ) ; D$2 ; 100) )

Ezt másoljuk lefelé és jobbra.

 

Mi történik itt?

A HELYETTEsítjük a B4 cella szövegében a vesszőket 100 darab szóközzel. Ezáltal egy hosszú szöveget kapunk, amiben jó messze vannak egymástól a szövegrészek (aláhúztam, hogy jobban lássátok, szóközök vannak közte):

Személygépkocsi                                                                                                    BP                                                                                                    egy                                                                                                    322

Ebből a szövegből kivágunk a D2-ben található karakter pozíciótól kezdve 100 karaktert, majd megtisztítjuk a felesleges szóközöktől…. s így tovább, az E2 pozíció számával… csak másolni kell a formulát lefele és jobbra.

Olyan, mintha egy gumira fűznél gyöngyöket, aztán a gumit kinyújtanád és szétvagdosnád.

A számokra kell csak figyelni: legalább annyi szóköz kell, mint a leghosszabb szövegdarab hossza szorozva a szövegdarabok számával. (Szóval sok legyen. Akár 1000 :-)) A kivágott karakterek szám ugyanennyi. A pozíció számok pedig ennek többszörösei.

Itt a minta fájl a formulával – próbáljátok ki!


Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!


4 comments

Skip to comment form

  1. ny.janos

    A javasolt megoldás akkor is működik, ha nem egy konkrét meghatározott karakter, hanem egy karakterlánc sorozat mentén kell szétvágni az adatokat.
    Ha azonban egyetlen karakterről van szó, akkor felesleges túlbonyolításnak gondolom ezt a megoldást az excel beépített szövegből oszlopok menüpontjának használata helyett.

    1. Kris
      Kris

      Igen, akkor is működik, ha több karakterből áll az “elválasztó karakter”.
      Én általában előnyben részesítem a formula megoldásokat az Excel beépített megoldásaival szemben, mert dinamikusabbak – ha kicserélem az adatokat, nem kell újra végigmenni a menüpontokon – a formula rögtön elvégzi a szétvágást. Nálam ez a gyakori használati eset, de egyetértek, egyszeri esetben jó megoldás a szövegből oszlopok.

      1. Bottlik Gábor

        Kedve Krisz!

        És milyen megoldást lehet használni, egy olyan dokumentumban, ahol a lehetséges mezőkön belül is megjelenik a “határoló” karakter? Pl: az oszlop tartalma rendre: “egy termék neve”; “másik név”; “egy új termék neve”; stb. Illetve további kérdés lenne a számok ábrázolása: a pdf egymás alatt hozza a számokat (jobbra zárt), amik többféle nagyságrendűek lehetnek (százas, ezres, milliós stb).
        Pdf tartalma:
        sorsz.; megnevezés; db; egység ár; összesen;
        1. egy termék neve 10 1 250 Ft 12 500 Ft
        2. másik név 3 520 Ft 1 560 Ft
        3. egy új termék neve 100 30 Ft 30 000 Ft

        Ha ezt bemásolom az Excelbe a következő sorok születnek (minden oszlop köz egy szóközre vált):
        sorsz.; megnevezés; db; egység ár; összesen;
        1. egy termék neve 10 1 250 Ft 12 500 Ft
        2. másik név 3 520 Ft 1 560 Ft
        3. egy új termék neve 100 30 Ft 30 000 Ft

        Itt sajnos nem bővíthető “ész nélkül” a szöveg és nem szabdalható a mutatott módszer alapján, hiszen külön oszlopba kerül majd a szövegmező egyébként összetartozó részei, illetve a számok is szétesnek.
        Lehet itt valami hasonló megoldást találni, mint a posztban leírt?
        Üdvözlettel:

        Bottlik Gábor

        1. Kris
          Kris

          Szia!
          Igen, sajnos ez az olyan eset, amire nem jó a vázolt megoldás. VBA-val meg lehetne oldani a szöveg leválasztását, és az egységár és összesen is elválasztható a Ft mentén. Ami problémás lehet, az a db elkülönítése.
          Kérdés ilyenkor, hogy érdemes-e megírni a feldolgozást VBA-ban, vagy lehet találni jobb adatkinyerési módszert a pdf-ből. Meglátjuk, Gábornak van-e javaslata. :-)

Vélemény, hozzászólás?

Az email címet nem tesszük közzé. A kötelező mezőket * karakterrel jelöljük.

Az alábbi HTML kódok használhatóak: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

css.php