Text do sloupců je úžasná funkce v Excelu, která si zaslouží mnohem více kreditu, než obvykle.
Jak naznačuje jeho název, slouží k rozdělení textu do více sloupců. Pokud máte například křestní jméno a příjmení ve stejné buňce, můžete je použít k jejich rychlému rozdělení do dvou různých buněk.
To může být velmi užitečné, když získáváte data z databází nebo je importujete z jiných formátů souborů, jako je text nebo CSV.
V tomto kurzu se dozvíte o mnoha užitečných věcech, které lze provést pomocí aplikace Text do sloupců v aplikaci Excel.
Kde najít text do sloupců v aplikaci Excel
Chcete -li získat přístup k textu do sloupců, vyberte datovou sadu a přejděte na Data → Datové nástroje → Text do sloupců.
Otevře se Průvodce převodem textu na sloupce.
Tento průvodce má tři kroky a před rozdělením textu do sloupců trvá několik uživatelských vstupů (v následujících příkladech uvidíte, jak lze tyto různé možnosti použít).
Pro přístup k textu do sloupců můžete také použít klávesovou zkratku - ALT + A + E.
Nyní se ponořme a podívejme se na úžasné věci, které můžete s textem do sloupců v Excelu dělat.
Příklad 1 - Rozdělte jména na křestní jméno a příjmení
Předpokládejme, že máte datovou sadu, jak je uvedeno níže:
Chcete -li rychle rozdělit křestní jméno a příjmení a dostat je do samostatných buněk, postupujte podle následujících kroků:
- Vyberte datovou sadu.
- Přejděte na Data → Datové nástroje → Text do sloupců. Otevře se Průvodce převodem textu na sloupce.
- V kroku 1 zkontrolujte, zda je vybrána možnost Oddělovač (což je také výchozí výběr). Klikněte na Další.
- V kroku 2 vyberte jako oddělovač „Mezerník“. Pokud máte podezření, že mezi názvy mohou být dvojité/trojité mezery za sebou, vyberte také možnost „Považovat po sobě jdoucí oddělovače jako jeden“. Klikněte na Další.
- V kroku 3 vyberte cílovou buňku. Pokud nevyberete cílovou buňku, přepíše vaši stávající sadu dat křestním jménem v prvním sloupci a příjmením v sousedním sloupci. Pokud chcete zachovat původní data neporušená, vytvořte kopii nebo vyberte jinou cílovou buňku.
- Klikněte na Dokončit.
To by vám okamžitě poskytlo výsledky s křestním jménem v jednom sloupci a příjmením v jiném sloupci.
Poznámka:
- Tato technika funguje dobře, když jméno tvoří pouze křestní jméno a příjmení. V případě, že existují iniciály nebo prostřední jména, pak to nemusí fungovat. Kliknutím sem zobrazíte podrobného průvodce, jak řešit případy s různými kombinacemi jmen.
- Výsledek, který získáte z použití funkce Text na sloupce, je statický. To znamená, že pokud dojde k nějakým změnám v původních datech, budete muset proces zopakovat, abyste získali aktualizované výsledky.
Čtěte také: Jak řadit podle příjmení v Excelu
Příklad 2 - Rozdělit ID e -mailu na uživatelské jméno a doménové jméno
Text do sloupců vám umožňuje vybrat si vlastní oddělovač pro rozdělení textu.
To lze použít k rozdělení e -mailových adres na uživatelská jména a názvy domén, protože jsou odděleny znakem @.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže:
Toto jsou smyšlená e-mailová ID některých skvělých superhrdinů (kromě mě, jsem jen obyčejný člověk, který ztrácí čas na Netflixu).
Zde jsou kroky k rozdělení těchto uživatelských jmen a názvů domén pomocí funkce Text na sloupce.
- Vyberte datovou sadu.
- Přejděte na Data → Datové nástroje → Text do sloupců. Otevře se Průvodce převodem textu na sloupce.
- V kroku 1 zkontrolujte, zda je vybrána možnost Oddělovač (což je také výchozí výběr). Klikněte na Další.
- V kroku 2 vyberte Jiné a do pole napravo od něj zadejte @. Nezapomeňte zrušit výběr jakékoli jiné možnosti (pokud je zaškrtnuto). Klikněte na Další.
- Změňte cílovou buňku na tu, kde chcete výsledek.
- Klikněte na Dokončit.
To by rozdělilo e -mailovou adresu a dalo by vám jméno a příjmení do samostatných buněk.
Příklad 3 - Získejte kořenovou doménu z adresy URL
Pokud pracujete s webovými adresami URL, může být někdy nutné znát celkový počet jedinečných kořenových domén.
Například v případě http://www.google.com/example1 a http://google.com/example2 je kořenová doména stejná, což je www.google.com
Předpokládejme, že máte datovou sadu, jak je uvedeno níže:
Zde jsou kroky k získání kořenové domény z těchto adres URL:
- Vyberte datovou sadu.
- Přejděte na Data → Datové nástroje → Text do sloupců. Otevře se Průvodce převodem textu na sloupce.
- V kroku 1 zkontrolujte, zda je vybrána možnost Oddělovač (což je také výchozí výběr). Klikněte na Další.
- V kroku 2 vyberte Jiné a do pole napravo od něj zadejte / (lomítko). Nezapomeňte zrušit výběr jakékoli jiné možnosti (pokud je zaškrtnuto). Klikněte na Další.
- Změňte cílovou buňku na tu, kde chcete výsledek.
- Klikněte na Dokončit.
Tím by došlo k rozdělení adresy URL a získání kořenové domény (ve třetím sloupci, protože před ní byla dvě lomítka).
Pokud nyní chcete zjistit počet unikátních domén, odeberte duplikáty.
Poznámka: To funguje dobře, když máte všechny adresy URL, které mají na začátku http: //. Pokud tomu tak není, získáte kořenovou doménu v samotném prvním sloupci. Před použitím textu na sloupce je vhodné zajistit konzistentnost těchto adres URL.
Příklad 4 - Převést neplatné formáty data na platné formáty data
Pokud data získáváte z databází, jako je SAP/Oracle/Capital IQ, nebo je importujete z textového souboru, existuje možnost, že formát data je nesprávný (tj. Formát, který aplikace Excel nepovažuje za datum).
Existuje jen několik formátů, kterým aplikace Excel rozumí, a jakýkoli jiný formát je třeba převést do platného formátu, aby byl v aplikaci Excel použit.
Předpokládejme, že máte data v níže uvedeném formátu (která nejsou v platném formátu data v Excelu).
Zde je postup, jak je převést na platné formáty data:
- Vyberte datovou sadu.
- Přejděte na Data → Datové nástroje → Text do sloupců. Otevře se Průvodce převodem textu na sloupce.
- V kroku 1 zkontrolujte, zda je vybrána možnost Oddělovač (což je také výchozí výběr). Klikněte na Další.
- V kroku 2 zkontrolujte, zda je vybrána možnost ŽÁDNÝ oddělovač. Klikněte na Další.
- Ve formátu dat sloupce vyberte Datum a vyberte požadovaný formát (DMY by znamenalo datum měsíc a rok). Změňte také cílovou buňku na tu, kde chcete výsledek.
- Klikněte na Dokončit.
To by okamžitě převádělo tyto neplatné formáty data na platné formáty data, které můžete použít v Excelu.
Příklad 5 - Převod textu na čísla
Někdy při importu dat z databází nebo jiných formátů souborů jsou čísla převedena do textového formátu.
K tomu může dojít několika způsoby:
- Mít apostrof před číslem. To vede k tomu, že je číslo považováno za text.
- Získávání čísel v důsledku textových funkcí, jako je LEFT, RIGHT nebo MID.
Problém je v tom, že tato čísla (která jsou v textovém formátu) jsou funkcemi Excelu, jako jsou SUMA a PRŮMĚR, ignorována.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže, kde jsou čísla v textovém formátu (všimněte si, že jsou zarovnána vlevo).
Zde jsou kroky k převodu textu na čísla pomocí textu na sloupce
- Vyberte datovou sadu.
- Přejděte na Data → Datové nástroje → Text do sloupců. Otevře se Průvodce převodem textu na sloupce.
- V kroku 1 zkontrolujte, zda je vybrána možnost Oddělovač (což je také výchozí výběr). Klikněte na Další.
- V kroku 2 zkontrolujte, zda je vybrána možnost ŽÁDNÝ oddělovač. Klikněte na Další.
- Ve formátu dat sloupce vyberte Obecné. Změňte také cílovou buňku na tu, kde chcete výsledek.
- Klikněte na Dokončit.
Tím by se tato čísla převedla zpět do obecného formátu, který lze nyní použít ve vzorcích.
Příklad 6 - Extrahujte prvních pět znaků řetězce
Někdy může být nutné extrahovat prvních několik znaků řetězce. To může být případ, kdy máte transakční data a prvních pět znaků (nebo jakýkoli jiný počet znaků) představuje jedinečný identifikátor.
Například v níže uvedené sadě dat je prvních pět znaků jedinečných pro produktovou řadu.
Zde jsou kroky k rychlému extrahování prvních pěti znaků z těchto dat pomocí textu do sloupců:
- Vyberte datovou sadu.
- Přejděte na Data → Datové nástroje → Text do sloupců. Otevře se Průvodce převodem textu na sloupce.
- V kroku 1 se ujistěte, že je vybrána pevná šířka. Klikněte na Další.
- V kroku 2 v části Náhled dat přetáhněte svislou čáru a umístěte ji za 5 znaků do textu. Klikněte na Další.
- Změňte cílovou buňku na tu, kde chcete výsledek.
- Klikněte na Dokončit.
Tím by se vaše datová sada rozdělila a poskytlo by vám prvních pět znaků každého ID transakce v jednom sloupci a zbytek ve druhém sloupci.
Poznámka: Můžete také nastavit více než jednu svislou čáru k rozdělení dat do více než 2 sloupců. Oddělovač nastavíte jednoduše kliknutím kamkoli do oblasti Náhled dat a přetažením kurzoru.
Příklad 7 - Převod čísel s koncovým znaménkem mínus na záporná čísla
I když to není něco, s čím se můžete setkávat často, ale někdy se můžete přistihnout, že opravujete čísla za znaménky mínus a děláte tato čísla záporná.
Text do sloupců je perfektní způsob, jak to seřadit.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže:
Zde jsou kroky k převedení těchto koncových mínusů na záporná čísla:
- Vyberte datovou sadu.
- Přejděte na Data → Datové nástroje → Text do sloupců. Otevře se Průvodce převodem textu na sloupce.
- V kroku 1 klikněte na „Další“.
- V kroku 2 klikněte na „Další“.
- V kroku 3 klikněte na tlačítko Upřesnit.
- V dialogovém okně Pokročilé nastavení importu textu vyberte možnost „Koncové minus pro záporné číslo“. Klikněte na OK.
- Nastavte cílovou buňku.
- Klikněte na Dokončit.
Tím by se okamžitě umístilo znaménko mínus od konce čísla na jeho začátku. Nyní můžete tato čísla snadno použít ve vzorcích a výpočtech.