Rozdělení textu do různých sloupců pomocí funkcí

Rozdělení textu do různých sloupců pomocí funkcí

K manipulaci s řetězci textu v datech můžete použít textové funkce ZLEVA, ČÁST, ZPRAVA, HLEDAT a DÉLKA. Můžete například rozdělit jméno, druhé jméno a příjmení z jedné buňky do tří samostatných sloupců.

Klíčem k rozdělení částí jmen pomocí textových funkcí je pozice každého znaku v textovém řetězci. Pozice mezer v textovém řetězci jsou také důležité, protože označují začátky a konce součástí jmen v řetězci.

Pokud buňka například obsahuje pouze křestní jméno a příjmení, začíná příjmení za prvním výskytem mezery. Některá jména v seznamu mohou obsahovat druhá jména nebo jejich iniciály. V takovém případě bude příjmení začínat za druhým výskytem mezery.

V tomto článku si ukážeme, jak extrahovat různé součásti z celé řady různých formátů jmen pomocí těchto užitečných funkcí. Texty se dají rozdělit do různých sloupců také pomocí Průvodce převodem textu do sloupců.

Příklad jména

Popis

Křestní jméno

Druhé jméno

Příjmení

Přípona

1

Jeff Smith

Bez druhého jména

Jeff

Smith

2

Eric S. Kurjan

Jedna iniciála druhého jména

Eric

S.

Kurjan

3

Janaina B. G. Bueno

Dvě iniciály druhého jména

Janaina

B. G.

Bueno

4

Kahn, Wendy Beth

Nejdříve příjmení oddělené čárkou

Wendy

Beth

Kahn

5

Mary Kay D. Andersen

Křestní jméno složené ze dvou částí

Mary Kay

D.

Andersen

6

Paula Barreto de Mattos

Příjmení složené ze tří částí

Paula

Barreto de Mattos

7

James van Eaton

Příjmení složené ze dvou částí

James

van Eaton

8

Bacon Jr., Dan K.

Nejdříve příjmení a přípona oddělené čárkou

Dan

K.

Bacon

Jr.

9

Gary Altman III

S příponou

Gary

Altman

III

10

Mr. Ryan Ihrig

S předponou

Ryan

Ihrig

11

Julie Taft-Rider

Příjmení s pomlčkou

Julie

Taft-Rider

Poznámka : Na obrázcích v následujících příkladech je v celém jménu zvýrazněn znak, který je hledán funkcí HLEDAT.

V tomto příklad oddělíme dvě součásti: křestní jméno a příjmení. Tyto dvě části jména odděluje jedna mezera.

Zkopírujte buňky z tabulky a vložte je do excelového listu od buňky A1. Vzorec, který vidíte vlevo, se zobrazí z referenčních důvodů. Vzorec vpravo převede Excel automaticky na správný výsledek.

Tip:    Než vložíte data do listu, nastavte šířky sloupců A a B na 250.

Příklad jména

Popis

Jeff Smith

Bez druhého jména

Vzorec

Výsledek (křestní jméno)

'=ZLEVA(A2; HLEDAT(" ";A2;1))

=ZLEVA(A2; HLEDAT(" ";A2;1))

Vzorec

Výsledek (příjmení)

'=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;1))

=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;1))

  1. Křestní jméno

    Křestní jméno začíná prvním znakem v řetězci (J) a končí u pátého znaku (mezery). Vzorec vrátí 5 znaků v buňce A2 počínaje zleva.

    Vzorec pro extrahování jména

    Použití funkce HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice mezery v buňce A2 počínaje zleva.

  2. Příjmení

    Příjmení začíná za mezerou, pět znaků zleva, a končí posledním znakem napravo (h). Vzorec extrahuje 5 znaků z buňky A2 počínaje zprava.

    Vzorec pro extrahování příjmení

    Použití funkcí HLEDAT a DÉLKA k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice mezery v buňce A2 počínaje zleva (5).

  3. Spočítá se celková délka textového řetězce a od ní se odečte počet znaků zleva od první mezery zjištěný v prvním kroku.

V tomto příkladu je použito křestní jméno, iniciála druhého jména a příjmení. Jednotlivé součásti jména jsou odděleny mezerou.

Zkopírujte buňky z tabulky a vložte je do excelového listu od buňky A1. Vzorec, který vidíte vlevo, se zobrazí z referenčních důvodů. Vzorec vpravo převede Excel automaticky na správný výsledek.

Tip:    Než vložíte data do listu, nastavte šířky sloupců A a B na 250.

Příklad jména

Popis

Eric S. Kurjan

Jedna iniciála druhého jména

Vzorec

Výsledek (křestní jméno)

'=ZLEVA(A2; HLEDAT(" ";A2;1))

=ZLEVA(A2; HLEDAT(" ";A2;1))

Vzorec

Výsledek (iniciála druhého jména)

'=ČÁST(A2;HLEDAT(" ";A2;1)+1;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)-HLEDAT(" ";A2;1))

=ČÁST(A2;HLEDAT(" ";A2;1)+1;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)-HLEDAT(" ";A2;1))

Vzorec

Dynamický výsledek (příjmení)

'=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1))

=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1))

  1. Křestní jméno

    Křestní jméno začíná prvním znakem zleva (E) a končí u pátého znaku (první mezery). Vzorec extrahuje prvních 5 znaků v buňce A2 počínaje zleva.

    Vzorec pro oddělení jména, příjmení a iniciály prostředního jména

    Použití funkce HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice mezery v buňce A2 počínaje zleva (5).

  2. Druhé jméno

    Druhé jméno začíná šestým znakem (S) a končí osmým znakem (druhou mezerou). Tento vzorec obsahuje vnořené funkce HLEDAT, které slouží k nalezení druhého výskytu mezery.

    Vzorec extrahuje tři znaky počínaje šestým znakem.

    Podrobnosti vzorce pro oddělení jména, prostředního jména a příjmení

    Použití funkce HLEDAT k nalezení hodnoty parametru start:

    Hledá se číslo pozice první mezery v buňce A2 počínaje od prvního znaku zleva. (5)

  3. Připočtením čísla 1 se získá pozice znaku za první mezerou (S). Tato pozice je počáteční pozicí druhého jména (5 + 1 = 6).

    Použití vnořených funkcí HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje od prvního znaku zleva. (5)

  4. Přičtením 1 získáte pozici znaku za první mezerou (S). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání druhého výskytu mezery. (5 + 1 = 6)

  5. Hledá se druhý výskyt mezery v buňce A2 počínaje od šesté pozice (znaku S) zjištěné v kroku 4. Pořadové číslo tohoto znaku je koncovou pozicí druhého jména. (8)

  6. Hledá se číslo pozice mezery v buňce A2 počínaje od prvního znaku zleva. (5)

  7. Vezme se číslo pozice druhé mezery zjištěné v kroku 5 a odečte se číslo pozice první mezery zjištěné v kroku 6. Výsledkem je počet znaků, které funkce ČÁST extrahuje z textového řetězce počínaje od šesté pozice zjištěné ve druhém kroku (8 – 5 = 3).

  8. Příjmení

    Příjmení začíná šestým znakem zprava (K) a končí prvním znakem zprava (n). Vzorec obsahuje vnořené funkce HLEDAT, které hledají druhý a třetí výskyt mezery (jež se nalézají na páté a osmé pozici zleva).

    Vzorec extrahuje 6 znaků z buňky A2 počínaje zprava.

    Druhá funkce HLEDAT ve vzorci pro oddělení jména, prostředního jména a příjmení
  9. Použití funkce DÉLKA a vnořených funkcí HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice mezery v buňce A2 počínaje od prvního znaku zleva. (5)

  10. Přičtením 1 získáte pozici znaku za první mezerou (S). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání druhého výskytu mezery. (5 + 1 = 6)

  11. Hledá se druhý výskyt mezery v buňce A2 počínaje od šesté pozice (znaku S) nalezené v kroku 2. Pořadové číslo tohoto znaku je koncovou pozicí druhého jména (8).

  12. Spočítá se celková délka textového řetězce v buňce A2 a odečte se počet znaků od prvního znaku zleva po druhý výskyt mezery nalezený v kroku 3. Výsledkem je počet znaků, které budou z celého jména extrahovány směrem zprava. (14 – 8 = 6)

Zde je příklad, jak extrahovat dvě iniciály druhého jména. Jednotlivé části jména oddělují první a třetí mezera.

Zkopírujte buňky z tabulky a vložte je do excelového listu od buňky A1. Vzorec, který vidíte vlevo, se zobrazí z referenčních důvodů. Vzorec vpravo převede Excel automaticky na správný výsledek.

Tip:    Než vložíte data do listu, nastavte šířky sloupců A a B na 250.

Příklad jména

Popis

Janaina B. G. Bueno

Dvě iniciály druhého jména

Vzorec

Výsledek (křestní jméno)

'=ZLEVA(A2; HLEDAT(" ";A2;1))

=ZLEVA(A2; HLEDAT(" ";A2;1))

Vzorec

Výsledek (iniciály druhého jména)

'=ČÁST(A2;HLEDAT(" ";A2;1)+1;HLEDAT(" ";A2;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1)-HLEDAT(" ";A2;1))

=ČÁST(A2;HLEDAT(" ";A2;1)+1;HLEDAT(" ";A2;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1)-HLEDAT(" ";A2;1))

Vzorec

Dynamický výsledek (příjmení)

'=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1))

=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1))

  1. Křestní jméno

    Křestní jméno začíná prvním znakem zleva (J) a končí u šestého znaku (první mezery). Vzorec extrahuje prvních 8 znaků v buňce A2 počínaje zleva.

    Vzorec pro oddělení jména, příjmení a dvou iniciál prostředního jména

    Použití funkce HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (8)

  2. Druhé jméno

    Druhé jméno začíná devátým znakem (B) a končí čtrnáctým znakem (třetí mezerou). Vzorec obsahuje vnořené funkce HLEDAT, které hledají první, druhý a třetí výskyt mezery na osmé, jedenácté a čtrnácté pozici.

    Vzorec extrahuje pět znaků počínaje devátým znakem.

    Vzorec pro oddělení jména, příjmení a dvou iniciál prostředního jména

    Použití funkce HLEDAT k nalezení hodnoty parametru start:

    Hledá se číslo pozice první mezery v buňce A2 počínaje od prvního znaku zleva. (8)

  3. Přičtením 1 získáte pozici znaku za první mezerou (B). Tato číselná pozice je počáteční pozice druhého jména. (8 + 1 = 9)

    Použití vnořených funkcí HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje od prvního znaku zleva. (8)

  4. Přičtením 1 získáte pozici znaku za první mezerou (B). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání druhého výskytu mezery. (8 + 1 = 9)

  5. Hledá se druhá mezera v buňce A2 počínaje od deváté pozice (B) zjištěné ve čtvrtém kroku. (11)

  6. Připočtením čísla 1 se získá pozice znaku za druhou mezerou (G). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání třetího výskytu mezery (11 + 1 = 12).

  7. Hledá se třetí mezera v buňce A2 počínaje od dvanácté pozice zjištěné v šestém kroku (14).

  8. Hledá se číslo pozice první mezery v buňce A2. (8)

  9. Vezme se číslo pozice třetí mezery zjištěné v kroku 7 a odečte se číslo pozice první mezery nalezené v kroku 6. Výsledkem je počet znaků, které funkce ČÁST extrahuje z textového řetězce počínaje od deváté pozice zjištěné v kroku 2.

  10. Příjmení

    Příjmení začíná pátým znakem zprava (B) a končí prvním znakem zprava (o). Vzorec obsahuje vnořené funkce HLEDAT, které hledají první, druhý a třetí výskyt mezery.

    Vzorec extrahuje 5 znaků z buňky A2 počínaje zprava.

    Vzorec pro oddělení jména, příjmení a dvou iniciál prostředního jména

    Použití funkce DÉLKA a vnořených funkcí HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje od prvního znaku zleva. (8)

  11. Přičtením 1 získáte pozici znaku za první mezerou (B). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání druhého výskytu mezery. (8 + 1 = 9)

  12. Hledá se druhá mezera v buňce A2 počínaje od deváté pozice (B) zjištěné ve druhém kroku (11).

  13. Přičtením 1 získáte pozici znaku za druhou mezerou (G). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání třetího výskytu mezery. (11 + 1 = 12)

  14. Hledá se třetí mezera v buňce A2 počínaje od dvanácté pozice (G) zjištěné v šestém kroku (14).

  15. Spočítá se celková délka textového řetězce v buňce A2 a odečte se počet znaků od prvního znaku zleva po třetí mezeru nalezenou v kroku 5. Výsledkem je počet znaků, které budou z celého jména extrahovány směrem zprava (19 - 14 = 5).

V tomto příkladu je příjmení uvedeno před křestním jménem a druhé jméno se vyskytuje na konci. Konec příjmení je signalizován čárkou a jednotlivé části jména jsou odděleny mezerami.

Zkopírujte buňky z tabulky a vložte je do excelového listu od buňky A1. Vzorec, který vidíte vlevo, se zobrazí z referenčních důvodů. Vzorec vpravo převede Excel automaticky na správný výsledek.

Tip:    Než vložíte data do listu, nastavte šířky sloupců A a B na 250.

Příklad jména

Popis

Kahn, Wendy Beth

Nejdříve příjmení oddělené čárkou

Vzorec

Výsledek (křestní jméno)

'=ČÁST(A2;HLEDAT(" ";A2;1)+1;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)-HLEDAT(" ";A2;1))

=ČÁST(A2;HLEDAT(" ";A2;1)+1;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)-HLEDAT(" ";A2;1))

Vzorec

Výsledek (druhé jméno)

'=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1))

=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1))

Vzorec

Dynamický výsledek (příjmení)

'=ZLEVA(A2; HLEDAT(" ";A2;1)-2)

=ZLEVA(A2; HLEDAT(" ";A2;1)-2)

  1. Křestní jméno

    Křestní jméno začíná sedmým znakem zleva (W) a končí u dvanáctého znaku (druhé mezery). Vzhledem k tomu, že se křestní jméno vyskytuje v celém jméně uprostřed, je třeba k jeho extrahování použít funkci ČÁST.

    Vzorec extrahuje šest znaků počínaje sedmým znakem.

    Vzorec pro oddělení příjmení, za kterým je křestní a prostřední jméno

    Použití funkce HLEDAT k nalezení hodnoty parametru start:

    Hledá se číslo pozice první mezery v buňce A2 počínaje od prvního znaku zleva (6).

  2. Přičtením 1 získáte pozici znaku za první mezerou (W). Tato číselná pozice je počáteční pozice prvního jména. (6 + 1 = 7)

    Použití vnořených funkcí HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje od prvního znaku zleva (6).

  3. Přičtením 1 získáte pozici znaku za první mezerou (W). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání druhého výskytu mezery. (6 + 1 = 7)

    Hledá se druhá mezera v buňce A2 počínaje od sedmé pozice (W) zjištěné ve čtvrtém kroku (12).

  4. Hledá se číslo pozice první mezery v buňce A2 počínaje od prvního znaku zleva (6).

  5. Vezme se číslo pozice druhé mezery zjištěné v kroku 5 a odečte se číslo pozice první mezery zjištěné v kroku 6. Výsledkem je počet znaků, které funkce ČÁST extrahuje z textového řetězce počínaje od sedmé pozice zjištěné ve druhém kroku (12 - 6 = 6).

  6. Druhé jméno

    Druhé jméno začíná čtvrtým znakem zprava (B) a končí prvním znakem zprava (h). Vzorec obsahuje vnořené funkce HLEDAT, které hledají první a druhý výskyt mezery na šesté a dvanácté pozici zleva.

    Vzorec extrahuje čtyři znaky zprava.

    Vzorec pro oddělení příjmení, za kterým je jméno a prostřední jméno

    Použití funkce DÉLKA a vnořených funkcí HLEDAT k nalezení hodnoty parametru start:

    Hledá se číslo pozice první mezery v buňce A2 počínaje od prvního znaku zleva (6).

  7. Přičtením 1 získáte pozici znaku za první mezerou (W). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání druhého výskytu mezery. (6 + 1 = 7)

  8. Hledá se druhý výskyt mezery v buňce A2 počínaje od sedmé pozice (W) zjištěné ve druhém kroku (12).

  9. Spočítá se celková délka textového řetězce v buňce A2 a odečte se počet znaků od prvního znaku zleva po druhou mezeru zjištěnou v kroku 3. Výsledkem je počet znaků, které budou z celého jména extrahovány směrem zprava. (16 - 12 = 4)

  10. Příjmení

    Příjmení začíná prvním znakem zleva (K) a končí čtvrtým znakem (n). Vzorec extrahuje čtyři znaky zleva.

    Vzorec pro oddělení příjmení, za kterým je jméno a prostřední jméno

    Použití funkce HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje od prvního znaku zleva (6).

  11. Odečtením čísla 2 se získá číslo pozice koncového znaku příjmení (n). Výsledkem je počet znaků, které bude funkce ZLEVA extrahovat (6 - 2 =4).

Tento příklad používá křestní jméno ze dvou částí, Mary Kay. Jednotlivé části jména oddělují druhá a třetí mezera.

Zkopírujte buňky z tabulky a vložte je do excelového listu od buňky A1. Vzorec, který vidíte vlevo, se zobrazí z referenčních důvodů. Vzorec vpravo převede Excel automaticky na správný výsledek.

Tip:    Než vložíte data do listu, nastavte šířky sloupců A a B na 250.

Příklad jména

Popis

Mary Kay D. Andersen

Křestní jméno složené ze dvou částí

Vzorec

Výsledek (křestní jméno)

'=ZLEVA(A2; HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1))

=ZLEVA(A2; HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1))

Vzorec

Výsledek (iniciála druhého jména)

'=ČÁST(A2;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1;HLEDAT(" ";A2;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1)-(HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1))

=ČÁST(A2;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1;HLEDAT(" ";A2;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1)-(HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1))

Vzorec

Dynamický výsledek (příjmení)

'=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1))

=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1))

  1. Křestní jméno

    Křestní jméno začíná prvním znakem zleva a končí u devátého znaku (druhé mezery). Tento vzorec obsahuje vnořené funkce HLEDAT, které slouží k nalezení druhého výskytu mezery zleva.

    Vzorec extrahuje devět znaků zleva.

    Vzorec pro oddělení jména, prostředního jména, iniciály prostředního jména a příjmení

    Použití vnořených funkcí HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje od prvního znaku zleva. (5)

  2. Přičtením 1 získáte pozici znaku za první mezerou (K). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání druhého výskytu mezery. (5 + 1 = 6)

  3. Hledá se druhý výskyt mezery v buňce A2 počínaje od šesté pozice (znaku K) zjištěné v kroku 2. Výsledkem je počet znaků, který bude z textového řetězce extrahován funkcí ZLEVA. (9)

  4. Druhé jméno

    Druhé jméno začíná desátým znakem (D) a končí dvanáctým znakem (třetí mezerou). Vzorec obsahuje vnořené funkce HLEDAT, které hledají první, druhý a třetí výskyt mezery.

    Vzorec extrahuje dva znaky uprostřed textového řetězce počínaje desátou pozicí.

    Vzorec pro oddělení křestního jména, prostředního jména, iniciály prostředního jména a příjmení

    Použití vnořených funkcí HLEDAT k nalezení hodnoty parametru start:

    Hledá se číslo pozice první mezery v buňce A2 počínaje od prvního znaku zleva. (5)

  5. Přičtením 1 získáte znak za první mezerou (K). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání druhého výskytu mezery. (5 + 1 = 6)

  6. Hledá se pozice druhého výskytu mezery v buňce A2 počínaje od šesté pozice (znaku K) zjištěné v kroku 2. Výsledkem je počet znaků, který bude z textového řetězce extrahován funkcí ZLEVA. (9)

  7. Přičtením 1 získáte znak za druhou mezerou (D). Výsledkem je počáteční pozice druhého jména. (9 + 1 = 10)

    Použití vnořených funkcí HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice znaku za druhou mezerou (D). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání třetí mezery (10).

  8. Hledá se číslo pozice třetí mezery v buňce A2 počínaje zleva. Výsledkem je koncová pozice druhého jména (12).

  9. Hledá se číslo pozice znaku za druhou mezerou (D). Výsledkem je počáteční pozice druhého jména (10).

  10. Vezme se číslo pozice třetí mezery zjištěné v kroku 6 a odečte se číslo pozice znaku D nalezené v kroku 7. Výsledkem je počet znaků, které funkce ČÁST extrahuje z textového řetězce počínaje od desáté pozice nalezené ve čtvrtém kroku (12 - 10 = 2).

  11. Příjmení

    Příjmení začíná osmým znakem zprava. Vzorec obsahuje vnořené funkce HLEDAT, které hledají první, druhý a třetí výskyt mezery na páté, deváté a dvanácté pozici.

    Vzorec extrahuje osm znaků zprava.

    Vzorec pro oddělení jména, prostředního jména, iniciály prostředního jména a příjmení

    Použití funkce DÉLKA a vnořených funkcí HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (5)

  12. Přičtením 1 získáte znak za první mezerou (K). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání výskytu mezery. (5 + 1 = 6)

  13. Hledá se druhá mezera v buňce A2 počínaje od šesté pozice (K) zjištěné ve druhém kroku (9).

  14. Připočtením čísla 1 se získá pozice znaku za druhou mezerou (D). Výsledkem je počáteční pozice druhého jména (9 + 1 = 10).

  15. Hledá se číslo pozice třetí mezery v buňce A2 počínaje zleva. Výsledkem je koncová pozice druhého jména (12).

  16. Spočítá se celková délka textového řetězce v buňce A2 a odečte se počet znaků od prvního znaku zleva po třetí mezeru nalezenou v kroku 5. Výsledkem je počet znaků, které budou z celého jména extrahovány směrem zprava (20 - 12 = 8).

Tento příklad používá příjmení ze tří částí: Barreto de Mattos. Konec křestního jména a začátek příjmení je signalizován první mezerou.

Zkopírujte buňky z tabulky a vložte je do excelového listu od buňky A1. Vzorec, který vidíte vlevo, se zobrazí z referenčních důvodů. Vzorec vpravo převede Excel automaticky na správný výsledek.

Tip:    Než vložíte data do listu, nastavte šířky sloupců A a B na 250.

Příklad jména

Popis

Paula Barreto de Mattos

Příjmení složené ze tří částí

Vzorec

Výsledek (křestní jméno)

'=ZLEVA(A2; HLEDAT(" ";A2;1))

=ZLEVA(A2; HLEDAT(" ";A2;1))

Vzorec

Výsledek (příjmení)

'=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;1))

=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;1))

  1. Křestní jméno

    Křestní jméno začíná prvním znakem zleva (P) a končí u šestého znaku (první mezery). Vzorec extrahuje šest znaků zleva.

    Vzorec pro oddělení jména a příjmení, které se skládá ze tří částí

    Použití funkce HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (6)

  2. Příjmení

    Příjmení začíná sedmnáctým znakem zprava (B) a končí prvním znakem zprava (s). Vzorec extrahuje sedmnáct znaků zprava.

    Vzorec pro oddělení jména a příjmení, které se skládá ze tří částí

    Použití funkcí HLEDAT a DÉLKA k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (6)

  3. Spočítá se celková délka textového řetězce v buňce A2 a odečte se počet znaků od prvního znaku zleva po první mezeru nalezenou v kroku 1. Výsledkem je počet znaků, které budou z celého jména extrahovány směrem zprava (23 - 6 = 17).

Tento příklad používá příjmení ze tří částí: van Eaton. Konec křestního jména a začátek příjmení je signalizován první mezerou.

Zkopírujte buňky z tabulky a vložte je do excelového listu od buňky A1. Vzorec, který vidíte vlevo, se zobrazí z referenčních důvodů. Vzorec vpravo převede Excel automaticky na správný výsledek.

Tip:    Než vložíte data do listu, nastavte šířky sloupců A a B na 250.

Příklad jména

Popis

James van Eaton

Příjmení složené ze dvou částí

Vzorec

Výsledek (křestní jméno)

'=ZLEVA(A2; HLEDAT(" ";A2;1))

=ZLEVA(A2; HLEDAT(" ";A2;1))

Vzorec

Výsledek (příjmení)

'=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;1))

=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;1))

  1. Křestní jméno

    Křestní jméno začíná prvním znakem zleva (J) a končí u šestého znaku (první mezery). Vzorec extrahuje šest znaků zleva.

    Vzorec pro oddělení jména a příjmení, které se skládá ze dvou částí

    Použití funkce HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (6)

  2. Příjmení

    Příjmení začíná devátým znakem zprava (v) a končí prvním znakem zprava (n). Vzorec z celého jména extrahuje 9 znaků zleva.

    Vzorec pro oddělení jména a příjmení, které se skládá ze dvou částí

    Použití funkcí HLEDAT a DÉLKA k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (6)

  3. Spočítá se celková délka textového řetězce v buňce A2 a odečte se počet znaků od prvního znaku zleva po první mezeru nalezenou v kroku 1. Výsledkem je počet znaků, které budou z celého jména extrahovány směrem zprava (15 - 6 = 9).

V tomto příkladu je nejdříve uvedené příjmení následované příponou. Čárka odděluje příjmení a příponu od křestního jména a iniciály druhého jména.

Zkopírujte buňky z tabulky a vložte je do excelového listu od buňky A1. Vzorec, který vidíte vlevo, se zobrazí z referenčních důvodů. Vzorec vpravo převede Excel automaticky na správný výsledek.

Tip:    Než vložíte data do listu, nastavte šířky sloupců A a B na 250.

Příklad jména

Popis

Bacon Jr., Dan K.

Nejdříve příjmení a přípona oddělené čárkou

Vzorec

Výsledek (křestní jméno)

'=ČÁST(A2;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1;HLEDAT(" ";A2;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1)-HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1))

=ČÁST(A2;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1;HLEDAT(" ";A2;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1)-HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1))

Vzorec

Výsledek (iniciála druhého jména)

'=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1))

=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)+1))

Vzorec

Výsledek (příjmení)

'=ZLEVA(A2; HLEDAT(" ";A2;1))

=ZLEVA(A2; HLEDAT(" ";A2;1))

Vzorec

Výsledek (přípona)

'=ČÁST(A2;HLEDAT(" "; A2;1)+1;(HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)-2)-HLEDAT(" ";A2;1))

=ČÁST(A2;HLEDAT(" "; A2;1)+1;(HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)-2)-HLEDAT(" ";A2;1))

  1. Křestní jméno

    Křestní jméno začíná dvanáctým znakem (D) a končí u patnáctého znaku (třetí mezery). Vzorec extrahuje tři znaky počínaje dvanáctým znakem.

    Vzorec pro oddělení příjmení a generační přípony uvedených nejdřív a oddělených čárkou

    Použití vnořených funkcí HLEDAT k nalezení hodnoty parametru start:

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (6)

  2. Přičtením 1 získáte znak za první mezerou (J). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání druhého výskytu mezery. (6 + 1 = 7)

  3. Hledá se druhá mezera v buňce A2 počínaje od sedmé pozice (J) zjištěné ve druhém kroku (11).

  4. Připočtením čísla 1 se získá pozice znaku za druhou mezerou (D). Výsledkem je počáteční pozice křestního jména (11 + 1 = 12).

    Použití vnořených funkcí HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice znaku za druhou mezerou (D). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání třetí mezery (12).

  5. Hledá se číslo pozice třetí mezery v buňce A2 počínaje zleva. Výsledkem je koncová pozice křestního jména (15).

  6. Hledá se číslo pozice znaku za druhou mezerou (D). Výsledkem je počáteční pozice křestního jména (12).

  7. Vezme se číslo pozice třetí mezery zjištěné v kroku 6 a odečte se číslo pozice znaku D zjištěného v kroku 7. Výsledkem je počet znaků, které funkce ČÁST extrahuje z textového řetězce počínaje od dvanácté pozice zjištěné ve čtvrtém kroku (15 - 12 = 3).

  8. Druhé jméno

    Druhé jméno začíná druhým znakem zprava (K). Vzorec extrahuje dva znaky zprava.

    Vzorec pro oddělení příjmení a generační přípony uvedených nejdřív a oddělených čárkou

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (6)

  9. Přičtením 1 získáte znak za první mezerou (J). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání druhého výskytu mezery. (6 + 1 = 7)

  10. Hledá se druhá mezera v buňce A2 počínaje od sedmé pozice (J) zjištěné ve druhém kroku (11).

  11. Připočtením čísla 1 se získá pozice znaku za druhou mezerou (D). Výsledkem je počáteční pozice křestního jména (11 + 1 = 12).

  12. Hledá se číslo pozice třetí mezery v buňce A2 počínaje zleva. Výsledkem je koncová pozice druhého jména (15).

  13. Spočítá se celková délka textového řetězce v buňce A2 a odečte se počet znaků od prvního znaku zleva po třetí mezeru nalezenou v kroku 5. Výsledkem je počet znaků, které budou z celého jména extrahovány směrem zprava (17 - 15 = 2).

  14. Příjmení

    Příjmení začíná prvním znakem zleva (B) a končí u šestého znaku (první mezery). Vzorec proto extrahuje šest znaků zleva.

    Vzorec pro oddělení příjmení a generační přípony uvedených nejdřív a oddělených čárkou

    Použití funkce HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (6)

  15. Přípona

    Přípona začíná sedmým znakem zleva (J) a končí devátým znakem zleva (.). Vzorec extrahuje tři znaky počínaje sedmým znakem.

    Vzorec pro oddělení příjmení a generační přípony uvedených nejdřív a oddělených čárkou

    Použití funkce HLEDAT k nalezení hodnoty parametru start:

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (6)

  16. Přičtením 1 získáte znak za první mezerou (J). Výsledkem je počáteční pozice přípony. (6 + 1 = 7)

    Použití vnořených funkcí HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (6)

  17. Připočtením čísla 1 se získá číslo pozice znaku za první mezerou (J). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání druhé mezery (7).

  18. Hledá se číslo pozice druhé mezery v buňce A2 počínaje od sedmého znaku (J) zjištěného ve čtvrtém kroku (11).

  19. Odečtením čísla 1 od pořadového čísla druhé mezery nalezeného v kroku 4 se získá pořadové číslo znaku “,”. Výsledkem je koncová pozice přípony (11 - 1 = 10).

  20. Hledá se číslo pozice první mezery. (6)

  21. Po nalezení první mezery přičtěte 1 pro nalezení následujícího znaku (J), který byl rovněž nalezen v krocích 3 a 4. (7)

  22. Vezme se číslo pozice znaku “,” nalezené v kroku 6 a odečte se číslo pozice znaku “J” nalezené v krocích 3 a 4. Výsledkem je počet znaků, které funkce ČÁST extrahuje z textového řetězce počínaje od sedmé pozice nalezené ve druhém kroku (10 - 7 = 3).

V tomto příkladu je první jméno na začátku řetězce a přípona na konci, takže lze použít vzorce podobné příkladu 2: Pomocí funkce ZLEVA extrahujte první jméno, pomocí funkce ČÁST extrahuje příjmení a pomocí funkce ZPRAVA extrahujte příponu.

Zkopírujte buňky z tabulky a vložte je do excelového listu od buňky A1. Vzorec, který vidíte vlevo, se zobrazí z referenčních důvodů. Vzorec vpravo převede Excel automaticky na správný výsledek.

Tip:    Než vložíte data do listu, nastavte šířky sloupců A a B na 250.

Příklad jména

Popis

Gary Altman III

Křestní jméno a příjmení s příponou

Vzorec

Výsledek (křestní jméno)

'=ZLEVA(A2; HLEDAT(" ";A2;1))

=ZLEVA(A2; HLEDAT(" ";A2;1))

Vzorec

Výsledek (příjmení)

'=ČÁST(A2;HLEDAT(" ";A2;1)+1;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)-(HLEDAT(" ";A2;1)+1))

=ČÁST(A2;HLEDAT(" ";A2;1)+1;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)-(HLEDAT(" ";A2;1)+1))

Vzorec

Výsledek (přípona)

'=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1))

=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1))

  1. Křestní jméno

    Křestní jméno začíná prvním znakem zleva (G) a končí u pátého znaku (první mezery). Vzorec proto z celého jména extrahuje pět znaků zleva.

    Vzorec pro oddělení jména a příjmení, za kterým následuje generační přípona

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (5)

  2. Příjmení

    Příjmení začíná šestým znakem zleva (A) a končí u jedenáctého znaku (druhé mezery). Vzorec obsahuje vnořené funkce HLEDAT, které sloužení k nalezení pozic mezer.

    Vzorec extrahuje šest znaků uprostřed textového řetězce počínaje šestou pozicí.

    Vzorec pro oddělení jména a příjmení, za kterým následuje generační přípona

    Použití funkce HLEDAT k nalezení hodnoty parametru start:

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (5)

  3. Přičtením 1 získáte pozici znaku za první mezerou (A). Výsledkem je počáteční pozice příjmení. (5 + 1 = 6)

    Použití vnořených funkcí HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (5)

  4. Přičtením 1 získáte pozici znaku za první mezerou (A). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání druhého výskytu mezery. (5 + 1 = 6)

  5. Hledá se číslo pozice druhé mezery v buňce A2 počínaje od šestého znaku nalezeného v kroku 4. Pořadové číslo tohoto znaku je koncovou pozicí příjmení (12).

  6. Hledá se číslo pozice první mezery. (5)

  7. Přičtením 1 najdete číslo pozice znaku za první mezerou (A), které bylo nalezeno také ve třetím a čtvrtém kroku. (6)

  8. Vezme se číslo pozice druhé mezery nalezené v kroku 5 a odečte se číslo pozice znaku A nalezené v krocích 6 a 7. Výsledkem je počet znaků, které funkce ČÁST extrahuje z textového řetězce počínaje od šesté pozice nalezené ve druhém kroku (12 - 6 = 6).

  9. Přípona

    Přípona začíná třetím znakem zprava. Vzorec obsahuje vnořené funkce HLEDAT, které sloužení k nalezení pozic mezer.

    Vzorec pro oddělení jména a příjmení, za kterým následuje generační přípona

    Použití funkce DÉLKA a vnořených funkcí HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (5)

  10. Přičtením 1 získáte znak za první mezerou (A). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání druhého výskytu mezery. (5 + 1 = 6)

  11. Hledá se druhá mezera v buňce A2 počínaje od šesté pozice (A) nalezené ve druhém kroku (12).

  12. Spočítá se celková délka textového řetězce v buňce A2 a odečte se počet znaků od prvního znaku zleva po druhou mezeru nalezenou v kroku 3. Výsledkem je počet znaků, které budou z celého jména extrahovány směrem zprava (15 - 12 = 3).

V tomto příkladu je před jménem předpona. Vzorce použité pro části jména jsou podobné příkladu 2. Křestní jméno lze extrahovat pomocí funkce ČÁST a příjmení pomocí funkce ZPRAVA.

Zkopírujte buňky z tabulky a vložte je do excelového listu od buňky A1. Vzorec, který vidíte vlevo, se zobrazí z referenčních důvodů. Vzorec vpravo převede Excel automaticky na správný výsledek.

Tip:    Než vložíte data do listu, nastavte šířky sloupců A a B na 250.

Příklad jména

Popis

Mr. Ryan Ihrig

S předponou

Vzorec

Výsledek (křestní jméno)

'=ČÁST(A2;HLEDAT(" ";A2;1)+1;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)-(HLEDAT(" ";A2;1)+1))

=ČÁST(A2;HLEDAT(" ";A2;1)+1;HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1)-(HLEDAT(" ";A2;1)+1))

Vzorec

Výsledek (příjmení)

'=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1))

=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;HLEDAT(" ";A2;1)+1))

  1. Křestní jméno

    Křestní jméno začíná pátým znakem zleva (R) a končí u devátého znaku (druhé mezery). Tento vzorec obsahuje vnořené funkce HLEDAT, které slouží k nalezení pozic mezer. Extrahuje čtyři znaky počínaje pátým znakem.

    Vzorec pro oddělení jména s oslovením

    Použití funkce HLEDAT k nalezení hodnoty parametru start:

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (4)

  2. Připočtením čísla 1 se získá pozice znaku za první mezerou (R). Výsledkem je počáteční pozice křestního jména (4 + 1 = 5).

    Použití vnořených funkcí HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (4)

  3. Přičtením 1 získáte pozici znaku za první mezerou (R). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání druhého výskytu mezery. (4 + 1 = 5)

  4. Hledá se číslo pozice druhé mezery v buňce A2 počínaje od pátého znaku nalezeného v krocích 3 a 4. Pořadové číslo tohoto znaku je koncovou pozicí křestního jména (9).

  5. Vyhledání první mezery. (4)

  6. Přičtením 1 najdete číslo pozice znaku za první mezerou (R), které bylo nalezeno také ve třetím a čtvrtém kroku. (5)

  7. Vezme se číslo pozice druhé mezery nalezené v kroku 5 a odečte se číslo pozice znaku R nalezené v krocích 6 a 7. Výsledkem je počet znaků, které funkce ČÁST extrahuje z textového řetězce počínaje od páté pozice nalezené ve druhém kroku (9 - 5 = 4).

  8. Příjmení

    Příjmení začíná pátým znakem zprava. Vzorec obsahuje vnořené funkce HLEDAT, které sloužení k nalezení pozic mezer.

    Vzorec pro oddělení jména s oslovením

    Použití funkce DÉLKA a vnořených funkcí HLEDAT k nalezení hodnoty parametru znaky:

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (4)

  9. Přičtením 1 získáte pozici znaku za první mezerou (R). Výsledkem je pořadové číslo znaku, od kterého bude zahájeno hledání druhého výskytu mezery. (4 + 1 = 5)

  10. Hledá se druhá mezera v buňce A2 počínaje od páté pozice (R) nalezené ve druhém kroku (9).

  11. Spočítá se celková délka textového řetězce v buňce A2 a odečte se počet znaků od prvního znaku zleva po druhou mezeru nalezenou v kroku 3. Výsledkem je počet znaků, které budou z celého jména extrahovány směrem zprava (14 - 9 = 5).

V tomto příkladu je použito příjmení s pomlčkou. Jednotlivé součásti jména jsou odděleny mezerou.

Zkopírujte buňky z tabulky a vložte je do excelového listu od buňky A1. Vzorec, který vidíte vlevo, se zobrazí z referenčních důvodů. Vzorec vpravo převede Excel automaticky na správný výsledek.

Tip:    Než vložíte data do listu, nastavte šířky sloupců A a B na 250.

Příklad jména

Popis

Julie Taft-Rider

Příjmení s pomlčkou

Vzorec

Výsledek (křestní jméno)

'=ZLEVA(A2; HLEDAT(" ";A2;1))

=ZLEVA(A2; HLEDAT(" ";A2;1))

Vzorec

Výsledek (příjmení)

'=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;1))

=ZPRAVA(A2;DÉLKA(A2)-HLEDAT(" ";A2;1))

  1. Křestní jméno

    Křestní jméno začíná prvním znakem zleva a končí u šestého znaku (první mezery). Vzorec extrahuje šest znaků zleva.

    Vzorec pro oddělení jména a příjmení, které obsahuje pomlčku

    Použití funkce HLEDAT k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice první mezery v buňce A2 počínaje zleva. (6)

  2. Příjmení

    Příjmení začíná desátým znakem zprava (T) a končí prvním znakem zprava (r).

    Vzorec pro oddělení jména a příjmení, které obsahuje pomlčku

    Použití funkcí HLEDAT a DÉLKA k nalezení hodnoty parametru počet_znaků:

    Hledá se číslo pozice mezery v buňce A2 počínaje od prvního znaku zleva. (6)

  3. Spočítá se celková délka textového řetězce a od ní se odečte počet znaků zleva po první mezeru zjištěnou v prvním kroku (16 - 6 = 10).

Viz taky

Rozdělení textu do sloupců pomocí Průvodce převodem textu do sloupců

Rozšiřte své znalosti a dovednosti
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

Děkujeme vám za zpětnou vazbu.

Děkujeme vám za váš názor. Vypadá to, že bude užitečné, když vás spojíme s některým z našich agentů z podpory Office.

×