Excel szövegdarabolás és tisztítás tanulmány

Az utóbbi hónapokban többedszerre szembesültem ugyanazzal a típusfeladattal. Mindig a „Quick & Dirty” megoldást választottam. Mindjárt kiderül mi az. Aztán az utolsó esetnél elgondolkotam, hogy hányféleképpen lehetne megoldani ezt a problémát, és egy elég nagy szám jött ki. Van benne egyszeri esetben alkalmazható és dinamikus, azaz újrahasznosítható is. Aztán arra gondoltam, hogy – mivel ilyet még nem csináltam- írok egy tanulmánynak is nevezjhető oktatóanyagot, ami meglehetősen sok mindent mutat meg az Excel hagyományos és újabb, tömbképleteket használó arcából egyaránt.

A feladat

Statikus megoldások – Quic & Dirty

1. AI chat

1. Kimásolom a fenti, átalakítandó adatokat szövegként vagy képként a vágólapra
2. Megnyitom a kedvenc AI chat partneremet
3. Megírom a prompt-ot és mellékelem a forrás adatot
4. Letöltöm az eredményt.
5. VOILÁ

Én ezt a prompt-ot írtam:

„Van egy listám Excelben, ami neveket és email címeket tartalmaz, és a csatolmány szerint van struktúrálva. Szükségem van egy kétoszlopos, Excelbe illeszthető listára, ami külön oszlopokban tartalmazza a neveket és az email címeket.”

És működött! Semmilyen Excel tudás nem kellett hozzá. Egy újabb ilyen esetben csak meg kell keresnem ezt a beszélgetést és beadni neki egy újabb listát.

Az eredmény természetesen statikus.

2. Villámkitöltés – Flashfill

1. Megvan az átalakítandó oszlopom egy tartományban
2. Az első sorban, a jobbra mellette lévő oszlopba beírom az erdményül kívánt nevet. Fontos, hogy pontosan úgy írjam le, ahogy a forrásban van.
3. Enter után nyomok egy Ctrl-E kombinációt, és az Excel kitölti az oszlopot az aljáig a nevekkel.
   
4. Jobbra a következő oszlop első cellájába beírom az első ember email címét
5. ENTER -> Ctrl-E -> Kitölti lefelé az email címeket, a kacsacsőröket, vesszőket nem viszi tovább.
   
6. KÉSZ

Ez az eredmény is statikus, azaz a forrás adat változása után újra kell futtatni a villámkitöltést

Dinamikus megoldások Excel képletekkel

2024-ben jelent meg az Excel-ben többek között 2 új függvény, amiket eredetileg SZÖVEGELŐTTE (TEXTBEFORE) és SZÖVEGUTÁNA (TEXTAFTER) néven lehetett használni.
Azonnal írtam is egy cikket róluk.

Aztán 2026-ban új magyar neveket kaptak, az angol nevük nem változott. ELŐTTELÉVŐSZÖVEG és UTÁNALÉVŐSZÖVEG. No Comment. Mindenesetre ennél a feladatnál bevethetők.

1. Hagyományos módszer

1. Az adatforrás melletti cellába a nyitó kacsacsőr előtti szöveg a maradó szóköz levágásával

   =KIMETSZ(ELŐTTELÉVŐSZÖVEG(A15;"<"))

2. Emellé pedig a nyitó kacsacsőr és a záró kacsacsőr közötti szöveg

   =ELŐTTELÉVŐSZÖVEG(UTÁNALÉVŐSZÖVEG(A15;"<");">")

3. Lehúzod a két képletet az aljáig és megvagyunk.

2. Tömbképletes módszer 1

Ezzel az egész oszlopra egyben fogja megcsinálni az eredményeket és egy egységként, tömbként kezeli Csak annyi a különbség az előzőhöz képest, hogy az adatforrás egész oszlopát adjuk meg forrásként. Beíráskor automatikusan kitöltődik az ajláig.

1. =KIMETSZ(ELŐTTELÉVŐSZÖVEG(A15:A22;"<"))

2. =ELŐTTELÉVŐSZÖVEG(UTÁNALÉVŐSZÖVEG(A15:A22;"<");">")

3. Tömbképletes módszer 2 – AI javaslat

Persze megkérdeztem az AI-t, hogy milyen képletet javasol. Kiderült, hogy az előző megoldás, ami két külön tömböt adott,  jó út volt, de lehet még ezt is fejleszteni, aminek az eredménye eléggé ijesztőnek tűnhet, de sokat is lehet tanulni belőle, ezért nem titkolom el. Felvonultatja a modern Excel újabb lehetőségeit is, mint a tömbök vízszintes összeolvasztása alias VÍZSZ.HALMOZÁS (HSTACK), illetve a LET függvényt is beveti, amiről meg itt írtam külön cikket.  Az eredmény egy darab tömb, ami az előző megoldás tömbjeit illeszti össze.

Az első cellába, ahol most Kovács Péter látható, oda írjuk az űrformulát, amit a jobb olvashatóság miatt betördeltem:

=LET(
adat;A27:A34;
nev;KIMETSZ(ELŐTTELÉVŐSZÖVEG(adat;"<"));
email;KIMETSZ(ELŐTTELÉVŐSZÖVEG(UTÁNALÉVŐSZÖVEG(adat;"<");">"));
VÍZSZ.HALMOZÁS(nev;email)
)

A LET függvény bevetésével az elején külön változóba teszi az egész adatforrást (adat), majd újabb két változóba az első és a második tömböt a nevekkel és email címekkel (nev, email), végül a kettőt egymás mellé illeszti. Ha követed az excel bázist, akkor mindent tudtál már előtte is, ez egy kiváló példa arra, hogyan kell profin alkalmazni a lehetőségeket. Már amennyiben ez az egy tömbös megoldás lenne a célod.

Megoldás PowerQuery-vel

Itt most csak jelezni szeretném, hogy PowerQuery-vel is nagyon gyorsan megoldható, ami ugye megjegyzi az átalakítás lépéseit és egy másik ugyanilyen adatsorra csak frissíteni kell és kész is van. Ha nem ismered még, akkor erősen javaslom. Van hozzá videótanfolyam is.

1. Alakítsd táblázattás az adatforrást
2. Kattints bele
3. Adatok szalag -> Táblázatból vagy tartományból
4. Elindul a PowerQuery szerkesztője, ahol pár lépéssel meg lehet csinálni az átalakítást
   

További lehetőségek

Lehetne rá VBA programot (makrót) írni, Office script is biztosan jó lehet és akár még Python kód is szóba jöhet.

De minek?

Csináltam hozzá videót is