Telefonszámunk: 1-472-0679

Szövegdarabolás függvényekkel

2024-01-21 - horvimi - Kategória: Függvények
Hivatkozott Excel verzio: Office365 Online

Az Excel-ben régóta megoldott a szövegdarabolás, hiszen az Adatok szalagon ott van a sokok által ismert  „Szövegből oszlopok” (T

ext to Columns”) varázsló. Ezzel valamilyen határoló jel mentén vagy adott karakterpozícióknál fel lehet darabolni egy szöveget, és a darabokat külön oszlopokba tudjuk tenni. Van ennek az eszköznek néhány speciális funkciója is, amikről készítettem anyagokat korábban.

Szövegből-oszlopok

Nagyon hasznos és rugalmasan használható eszköz, de csak értékekkel dolgozik. Ez azt jelenti, hogy a bemenete, azaz a darabolandó adat vagy oszlop nem lehet képlet (értékké kell alakítani), és a darabolás eredménye is mindig statikus, azaz értékeket ad kimenetként. Ha változik az adatforrás, akkor újra kell futtatni.

Szövegdarabolás hagyományos függvényekkel

Ha az adatforrás változhat vagy cserélődhet, esetleg az is már képlettel van kialakítva, vagy csak egy bizonyos részt szeretnénk kiemelni egy szövegből valamilyen szabály alapján, akkor formula megoldásra lehet szükségünk. Például

  • Valamilyen elválasztó karakter előtti vagy utáni szövegrész
  • Valamely karakter n-dik előfordulása utáni vagy előtti,
  • Valamely karakter utolsó előfordulása előtti vagy utáni,
  • Bizonyos elválasztó karakterek közötti,
  • stb…

Hagyományos módszerek

Az Excel minden verziójában megtalálható szövegkezelő függvényekkel.

  • BAL, JOBB, KÖZÉP,
  • SZÖVEG.KERES, HELYETTE, CSERE,
  • KIMETSZ, HOSSZ, SOKSZOR,
  • stb..

Ezeket kombinálva elég sok mindent meg lehet csinálni, természetesen egy szöveg szétválasztásához is használhatjuk őket, de gyakran meglehetősen bonyolult, többszörösen egymásba ágyazott függvényeket tartalmazó formulákat kell alkotni. Az Internet tele van ezekkel a megoldásokkal, hiszen az elmúlt évtizedekben felgyülemlettek a kérdések és a válaszok a témában. Csak jól kell tudnunk kérdezni…

Néhány példa – magyarázatok nélkül

Az első szóköz előtti rész kiszedése A1-ből

=BAL(A1;SZÖVEG.KERES(" ";A1)-1)

Az utolsó szóköz utáni rész kiszedése A1-ből – elrettentő de tiszteletre méltó

=JOBB(A1;HOSSZ(A1)-SZÖVEG.KERES("#";HELYETTE(A1;" ";"#";HOSSZ(A1)-HOSSZ(HELYETTE(A1;" ";"")))))

Szövegdarabolás Excel 365 függvényekkel

Ha van 365-ös verziód, akkor a fenti két feladat, és a többi hasonló, jelentősen leegyszerűsödik. 2022-ben mindenki számára megérkezett egy új függvénycsomag, amiben ez a három szövegfüggvény is benne van:

  • SZÖVEGELŐTTE – (TEXTBEFORE)
  • SZÖVEGUTÁNA – (TEXTAFTER)
  • SZÖVEGFELOSZTÁS – (TEXTSPLIT)

Már a nevük is elég sokat sejtető, és nem csalódunk bennük, ha közelebbről is megvizsgáljuk őket. Nem váltják ki  teljesen a szövegdaraboló varázslót, de néhány dolgot onnan is átemelnek.

SZÖVEGELŐTTE

Egy (vagy több) megadott szöveg (vagy karakter) előtti részt adja vissza egy megadott szövegből. Olyan, mint egy dinamikus BAL függvény.

=SZÖVEGELŐTTE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found] )
text – Az a szöveg, amit szét akarunk vágni
delimiter – Az a szöveg, ami a vágás helyét jelöli. Ez előtti részt fogjuk kapni.
instance_num – Megadhatjuk, hogy a delimiter hányadik előfordulásánál szeretnénk a vágást. Nem kötelező megadni, alapértéke = 1
Ha negatív számot adunk meg, akkor a végéről indítja a keresést visszafelé
match_mode – Kis és nagybetű érzékenységet állíthatunk be az elválasztóra. Nem kötelező, de alapértéke=0
  • 0 – Kis és nagybetűk különböznek
  • 1 – Kis és nagybetűk nem különböznek
match_end – A vágandó szöveg végére tegyen-e egy elválasztójelet. Nem kötelező, alapértéke=0
  • 0 – Nem nyúl a szöveghez. Ha nincs benne az elválasztó, akkor hibát ad, illetve az utolsó paraméter szerint viselkedik
  • 1 – A szöveg végére helyez egy elválasztót, mielőtt vágna. Így ha nincs benne egyszer sem az elválasztó, akkor a teljes szöveget adja vissza
if_not_found – Mit adjon vissza, ha nem talál egy elválasztót sem. Nem kötelező. Ha nem adjuk meg, akkor #HIÁNYZIK lesz a kimenet.

SZÖVEGELŐTTE példák

Az első szóköz előtti rész kiszedése A1-ből

=SZÖVEGELŐTTE(A1;" ")

Az utolsó szóköz előtti rész kiszedése A1-ből

=SZÖVEGELŐTTE(A1;" ";-1)

Második szóköz előtti rész

=SZÖVEGELŐTTE(A1;",";2)

Több lehetséges elválasztó előtti rész

Bármely, a kapcsos zárójelek között felsorolt karakter első előfordulása előtti részt fogja visszaadni

=SZÖVEGELŐTTE(A1;{",";" ,";"-";" -"})

SZÖVEGUTÁNA

Egy (vagy több) megadott szöveg (vagy karakter) utáni részt adja vissza egy megadott szövegből. Olyan, mint egy dinamikus JOBB függvény.

=SZÖVEGUTÁNA(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found] )

A paraméterezése teljesen megegyezik a SZÖVEGELŐTTE függvénynél leírtakkal.

SZÖVEGFELOSZTÁS

Ez a függvény helyettesítheti akár teljes mértékben a Szövegből oszlopok varázslót, mert ez képes több vágójel alapján több részre is szétszedni az eredeti szöveget, és az eredményt egy vízszintes tömbbe helyezi.

=SZÖVEGFELOSZTÁS(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
text – Az a szöveg, amit szét akarunk vágni
Col_delimiter – Az oszlopokra tördeléshez használt elválasztó karakter. Ha nem adjuk meg, akkor a sor elválasztót meg kell adni.
Row_delimiter – mely karakter mentén tördelje egymás alatti cellákba a szöveget.
Ignore_empty  – hogyan kezelje az egymás utáni azonos (üres) elválasztó jeleket
  • 0- HAMIS (alap) – Az üres elválasztók számára üres cellákat generál az eredmény tömbben
  • 1- IGAZ – Az üres elválasztókat figyelmen kívül hagyja
match_mode – Kis és nagybetű érzékenységet állíthatunk be az elválasztóra. Nem kötelező, de alapértéke=0 (Kis és nagybetűk különböznek)
  • 0 – Kis és nagybetűk különböznek
  • 1 – Kis és nagybetűk nem különböznek

SZÖVEGFELOSZTÁS példa

A megadott szöveget a szóközmentén darabolja egymás melletti cellákba. A darabok egy vízszintes, egységes tömböt alkotnak.

=SZÖVEGFELOSZTÁS(B32;" ")

Szovegfelosztas

Lássuk a videót

Kapcsolódó anyagok:

  1. Excel oszlop és sorválogatás függvénnyel
  2. Excel 2016 – Előrejelzési munkalap 2. rész
  3. LET függvény az Excelben
  4. Az Excel SZŰRŐ függvénye
  5. Üres sorok törlése Excelben makró nélkül
On 2024-01-21   /     /   Leave a comment

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