Mutatok 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.
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!
6 comments
Skip to comment form
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.
Author
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.
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
Author
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. 🙂
Szia!
Ettől a választól féltem. De azért köszönöm a választ. 🙂
A VBA megoldás lehet. Ha a teljes cella tartalmát betűről betűre átvizsgálom, keresve az ” 1″, ” 2″, ” 3″, …, ” 0″ karakterpárokat megtalálhatom a db oszlop kezdő pozícióját. Nyilván itt az lesz a buktató, ha már a megnevezés mezőbe kerül egy olyan szöveg, ami számot is tartalmaz (pl: “termék név 100g”, “termék név 200g”) 🙁 Tudom örök elégedetlen vagyok.
Üdvözlettel:
Bottlik Gábor
Hálás köszönet, nagy segítség volt.