7 lenyűgöző dolog, amit az Excel oszlopok szövege segíthet Önnek

A Szöveg oszlopokba egy csodálatos funkció az Excelben, amely sokkal több elismerést érdemel, mint általában.

Ahogy a neve is sugallja, a szöveget több oszlopra osztják. Például, ha ugyanabban a cellában van kereszt- és vezetéknév, akkor gyorsan két részre oszthatja ezeket.

Ez akkor lehet igazán hasznos, ha adatait adatbázisokból szerezi be, vagy más fájlformátumokból, például szövegből vagy CSV -ből importálja.

Ebben az oktatóanyagban sok hasznos dolgot fog megtudni, amelyeket az Excel Szöveg oszlopok használatával lehet elvégezni.

Hol találhat szöveget az oszlopokhoz az Excelben

Az oszlopok szövegének eléréséhez válassza ki az adatkészletet, majd lépjen az Adatok → Adateszközök → Szöveg oszlopokba menüpontra.

Ezzel megnyílik a Szöveg konvertálása oszlopokba varázsló.

Ez a varázsló három lépésből áll, és néhány felhasználói bevitelt végez, mielőtt a szöveget oszlopokra osztja (az alábbi példákban látni fogja, hogyan használhatók ezek a különböző lehetőségek).

Az oszlopok szövegének eléréséhez használhatja az ALT + A + E billentyűparancsot is.

Most merüljünk el, és nézzünk meg néhány csodálatos dolgot, amelyeket az Excel oszlopok szövegével tehet.

1. példa - A nevek felosztása keresztnévre és vezetéknévre

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel:

Ha gyorsan fel szeretné osztani a kereszt- és a vezetéknevet, és ezeket külön cellákba szeretné kapni, kövesse az alábbi lépéseket:

• Válassza ki az adatkészletet.
• Lépjen az Adatok → Adateszközök → Szöveg oszlopokba menüpontra. Ezzel megnyílik a Szöveg konvertálása oszlopokba varázsló.
• Az 1. lépésben győződjön meg arról, hogy a Elhatárolt elem van kiválasztva (ez egyben az alapértelmezett beállítás is). Kattintson a Tovább gombra.
• A 2. lépésben válassza a „Szóköz” -t elválasztóként. Ha gyanítja, hogy a nevek között kettős/hármas egymást követő szóköz lehet, válassza az „Egymást követő elválasztók kezelése egyként” lehetőséget is. Kattintson a Tovább gombra.
• A 3. lépésben válassza ki a célcellát. Ha nem választ ki célcellát, az felülírja a meglévő adathalmazt az első oszlopban szereplő vezetéknévvel és a szomszédos oszlop vezetéknevével. Ha meg szeretné őrizni az eredeti adatokat, akkor hozzon létre egy másolatot, vagy válasszon másik célcellát.
• Kattintson a Befejezés gombra.

Ez azonnal megadja az eredményeket, az egyik oszlopban a vezetéknévvel, a másik oszlopban pedig a vezetéknévvel.

Jegyzet:

• Ez a technika akkor működik jól, ha a név csak a kereszt- és a vezetéknévből áll. Ha vannak kezdőbetűk vagy középső nevek, akkor ez nem működik. Kattintson ide, ha részletes útmutatót szeretne kapni az esetek kezeléséről különböző névkombinációkkal.
• A Szöveg oszlopokba funkció használatával kapott eredmény statikus. Ez azt jelenti, hogy ha bármilyen változás történik az eredeti adatokban, meg kell ismételnie a folyamatot, hogy frissített eredményeket kapjon.

Olvassa el még: Hogyan rendezheti a vezetéknév szerint az Excelben

2. példa - Az e -mail azonosítók felosztása felhasználónévre és tartománynévre

A Szöveg oszlopokba opció lehetővé teszi, hogy saját elválasztóját válassza ki a szöveg felosztásához.

Ezzel az e -mail címek felhasználónevekre és tartománynevekre oszthatók, mivel ezeket a @ jel választja el egymástól.

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel:

Ezek néhány kitalált e-mail azonosító néhány klassz szuperhősnek (magam kivételével én csak egy rendszeres időpocsékolás a Netflix-en).

Az alábbi lépésekkel oszthatja fel ezeket a felhasználóneveket és tartományneveket a Szöveg oszlopok funkcióval.

• Válassza ki az adatkészletet.
• Lépjen az Adatok → Adateszközök → Szöveg oszlopokba menüpontra. Ezzel megnyílik a Szöveg konvertálása oszlopokba varázsló.
• Az 1. lépésben győződjön meg arról, hogy a Elhatárolt elem van kiválasztva (ez egyben az alapértelmezett beállítás is). Kattintson a Tovább gombra.
• A 2. lépésben válassza az Egyéb lehetőséget, és írja be a @ jelzést a jobb oldali mezőbe. Győződjön meg róla, hogy megszünteti bármely más lehetőség kijelölését (ha be van jelölve). Kattintson a Tovább gombra.
• Változtassa meg a célcellát arra, ahol az eredményt szeretné.
• Kattintson a Befejezés gombra.

Ez felosztja az e -mail címet, és külön cellákban adja meg a kereszt- és a vezetéknevet.

3. példa - Szerezze meg a root tartományt az URL -ből

Ha webes URL -ekkel dolgozik, előfordulhat, hogy ismernie kell az egyedi gyökértartományok teljes számát.

Például a http://www.google.com/example1 és a http://google.com/example2 esetében a gyökérdomain megegyezik, ami a www.google.com

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel:

Íme a lépések a root domain lekéréséhez ezekből az URL -ekből:

• Válassza ki az adatkészletet.
• Lépjen az Adatok → Adateszközök → Szöveg oszlopokba menüpontra. Ezzel megnyílik a Szöveg konvertálása oszlopokba varázsló.
• Az 1. lépésben győződjön meg arról, hogy a Elhatárolt elem van kiválasztva (ez egyben az alapértelmezett beállítás is). Kattintson a Tovább gombra.
• A 2. lépésben válassza az Egyéb lehetőséget, és írja be a / (előre perjel) mezőt a jobb oldali mezőbe. Győződjön meg róla, hogy megszünteti bármely más lehetőség kijelölését (ha be van jelölve). Kattintson a Tovább gombra.
• Változtassa meg a célcellát arra, ahol az eredményt szeretné.
• Kattintson a Befejezés gombra.

Ez feldarabolja az URL -t, és megadja a gyökértartományt (a harmadik oszlopban, mivel előtte két perjel volt).

Ha most szeretné megtalálni az egyedi tartományok számát, távolítsa el az ismétlődéseket.

Megjegyzés: Ez akkor működik jól, ha az összes olyan URL -címmel rendelkezik, amelynek elején szerepel a http: //. Ha nem, akkor a gyökértartományt az első oszlopban kapja meg. Jó gyakorlat, ha ezeket az URL -eket összhangba hozza, mielőtt szöveget oszlopokba használna.

4. példa - Érvénytelen dátumformátumok konvertálása érvényes dátumformátumokká

Ha adatait olyan adatbázisokból szerezte be, mint például az SAP/Oracle/Capital IQ, vagy szöveges fájlból importálja őket, akkor előfordulhat, hogy a dátumformátum helytelen (azaz olyan formátum, amelyet az Excel nem tekint dátumnak).

Az Excel csak néhány formátumot tud megérteni, és minden más formátumot érvényes formátumba kell konvertálni, hogy az Excelben használható legyen.

Tegyük fel, hogy az alábbi formátumban vannak dátumok (amelyek nem az érvényes Excel dátumformátumban vannak).

Íme a lépések ezek érvényes dátumformátumokká alakításához:

• Válassza ki az adatkészletet.
• Lépjen az Adatok → Adateszközök → Szöveg oszlopokba menüpontra. Ezzel megnyílik a Szöveg konvertálása oszlopokba varázsló.
• Az 1. lépésben győződjön meg arról, hogy a Elhatárolt elem van kiválasztva (ez egyben az alapértelmezett beállítás is). Kattintson a Tovább gombra.
• A 2. lépésben győződjön meg arról, hogy a NO határoló opció van kiválasztva. Kattintson a Tovább gombra.
• Az Oszlop adatformátumban válassza a Dátum lehetőséget, majd válassza ki a kívánt formátumot (DMY dátum hónapot és évet jelentene). Ezenkívül módosítsa a célcellát arra, ahol az eredményt szeretné.
• Kattintson a Befejezés gombra.

Ez azonnal átalakítja ezeket az érvénytelen dátumformátumokat érvényes dátumformátumokká, amelyeket használhat az Excelben.

5. példa - Szöveg konvertálása számokká

Néha, amikor adatokat importál adatbázisokból vagy más fájlformátumokból, a számok szöveges formátumba konvertálódnak.

Ennek több módja is lehet:

• A szám előtt aposztróf van. Ez ahhoz vezet, hogy a számot szövegként kell kezelni.
• Számok lekérése olyan szöveges funkciók eredményeként, mint a BAL, JOBB vagy MID.

A probléma ezzel az, hogy ezeket a számokat (amelyek szöveges formátumban vannak) figyelmen kívül hagyják az Excel olyan funkciói, mint a SUM és az ÁTLAG.

Tegyük fel, hogy van egy adathalmaza, amint az alább látható, ahol a számok szöveges formátumban vannak (vegye figyelembe, hogy ezek balra vannak igazítva).

Íme a lépések a Szöveg oszlopokká alakításához a szöveg számokká alakításához

• Válassza ki az adatkészletet.
• Lépjen az Adatok → Adateszközök → Szöveg oszlopokba menüpontra. Ezzel megnyílik a Szöveg konvertálása oszlopokba varázsló.
• Az 1. lépésben győződjön meg arról, hogy a Elhatárolt elem van kiválasztva (ez egyben az alapértelmezett beállítás is). Kattintson a Tovább gombra.
• A 2. lépésben győződjön meg arról, hogy a NO határoló opció van kiválasztva. Kattintson a Tovább gombra.
• Az Oszlop adatformátumban válassza az Általános lehetőséget. Ezenkívül módosítsa a célcellát arra, ahol az eredményt szeretné.
• Kattintson a Befejezés gombra.

Ez visszaállítaná ezeket a számokat általános formátumba, amely mostantól képletekben használható.

6. példa - Egy karakterlánc első öt karakterének kibontása

Néha előfordulhat, hogy ki kell vonnia a karakterlánc első néhány karakterét. Ez akkor fordulhat elő, ha tranzakciós adatokkal rendelkezik, és az első öt karakter (vagy bármilyen más karakterszám) egyedi azonosítót jelent.

Például az alább látható adatkészletben az első öt karakter egy termékcsaládra jellemző.

Az alábbi lépésekkel gyorsan kinyerheti az adatokból az első öt karaktert a Szöveg oszlopokba használatával:

• Válassza ki az adatkészletet.
• Lépjen az Adatok → Adateszközök → Szöveg oszlopokba menüpontra. Ezzel megnyílik a Szöveg konvertálása oszlopokba varázsló.
• Az 1. lépésben győződjön meg arról, hogy a Fix szélesség van kiválasztva. Kattintson a Tovább gombra.
• A 2. lépésben az Adat előnézet szakaszban húzza a függőleges vonalat, és helyezze el a szöveg 5 karaktere után. Kattintson a Tovább gombra.
• Változtassa meg a célcellát arra, ahol az eredményt szeretné.
• Kattintson a Befejezés gombra.

Ez felosztaná az adathalmazt, és minden tranzakcióazonosító első öt karakterét egy oszlopba kapná, és a második oszlopba.

Megjegyzés: Több függőleges vonalat is beállíthat úgy, hogy az adatokat több mint 2 oszlopra osztja fel. Csak kattintson bárhová az Adat -előnézeti területen, és húzza a kurzort az elválasztó beállításához.

Példa 7 - Számok konvertálása a mínusz előjellel negatív számokká

Bár ez nem olyasmi, amellyel gyakran, de néha találkozhat, előfordulhat, hogy a számokat záró mínuszjelekkel rögzíti, és negatívvá teszi.

A Szöveg oszlopokba tökéletes megoldás ennek rendezésére.

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel:

Az alábbi lépésekkel lehet ezeket a mínuszokat negatív számokká alakítani:

• Válassza ki az adatkészletet.
• Lépjen az Adatok → Adateszközök → Szöveg oszlopokba menüpontra. Ezzel megnyílik a Szöveg konvertálása oszlopokba varázsló.
• Az 1. lépésben kattintson a „Tovább” gombra.
• A 2. lépésben kattintson a „Tovább” gombra.
• A 3. lépésben kattintson a Speciális gombra.
• A Speciális szövegimportálási beállítások párbeszédpanelen válassza a „Követő mínusz negatív számra” lehetőséget. Kattintson az OK gombra.
• Állítsa be a célcellát.
• Kattintson a Befejezés gombra.

Ez azonnal elhelyezi a mínusz jelet az eleje számának végétől. Most könnyen használhatja ezeket a számokat képletekben és számításokban.