Jak přidat úvodní nuly v Excelu

Tento výukový program pojednává o různých metodách přidání nebo zachování úvodních nul a také odstranění úvodních nul v aplikaci Excel.

Kdykoli zadáte nebo importujete čísla s jednou nebo více úvodními nulami, například 000652, Excel tyto nuly automaticky odstraní a v buňkách se zobrazí pouze samotné číslo (652). Je to proto, že úvodní nuly nejsou nutné pro výpočty a nepočítají se.

Jsou však chvíle, kdy jsou úvodní nuly nezbytné, například když zadáváte identifikační čísla, telefonní čísla, čísla kreditních karet, kódy produktů nebo poštovní směrovací čísla atd. Naštěstí nám Excel nabízí několik způsobů, jak přidat nebo ponechat úvodní nuly. v buňkách. V tomto článku vám ukážeme různé způsoby, jak přidat nebo ponechat úvodní nuly a odstranit úvodní nuly.

Přidání úvodních nul v Excelu

V zásadě existují 2 metody, které můžete použít k přidání úvodních nul: jedna, naformátujte své číslo jako ‚Text‘; za druhé, použijte vlastní formátování k přidání úvodních nul. Metoda, kterou chcete použít, může záviset na tom, co chcete s číslem udělat.

Možná budete chtít přidat úvodní nulu, když zadáváte jedinečná čísla ID, čísla účtů, čísla sociálního zabezpečení nebo PSČ atd. Tato čísla však nebudete používat pro výpočty nebo ve funkcích, takže je nejlepší je převést čísla na text. Nikdy byste nesčítali ani nezprůměrovali telefonní čísla nebo čísla účtů.

Existuje několik způsobů, jak přidat nebo doplnit nuly před čísla tak, že je naformátujete jako text:

• Změna formátu buňky na Text
• Přidání apostrofu (‘)
• Použití funkce TEXT
• Použití funkce REPT/LEN
• Použijte funkci CONCATENATE/operátor ampersand (&)
• Použití funkce RIGHT

Změna formátu buňky na text

Toto je jeden z nejjednodušších způsobů, jak k číslům přidat úvodní nuly. Pokud se právě chystáte zadávat čísla a chcete při psaní ponechat úvodní nuly, pak je tato metoda právě pro vás. Změnou formátu buňky z Obecné nebo Číslo na Text můžete přinutit Excel, aby s vašimi čísly nakládal jako s textovými hodnotami a vše, co do buňky zadáte, zůstane úplně stejné. Postup:

Vyberte buňky, do kterých chcete přidat úvodní nuly. Přejděte na kartu ‚Domů‘, klikněte na rozevírací pole ‚Formát‘ ve skupině Čísla a z možností formátu vyberte ‚Text‘.

Nyní, když zadáte svá čísla, Excel z nich neodstraní žádnou úvodní nulu.

V levém horním rohu buňky můžete vidět malý zelený trojúhelník (indikátor chyby) a když tuto buňku vyberete, zobrazí se vám varovný znak označující, že jste číslo uložili jako text.

Chcete-li chybovou zprávu odstranit, vyberte buňku (buňky), klikněte na varovný znak a ze seznamu vyberte možnost Ignorovat chybu.

Můžete také zadat telefonní čísla s mezerou nebo pomlčkou mezi čísly, Excel s těmito čísly bude automaticky zacházet jako s textem.

Pomocí Leading apostrof ( ' )

Dalším způsobem, jak přidat úvodní nuly v Excelu, je přidat apostrof (‘) na začátek čísla. To přinutí Excel zadat číslo jako text.

Stačí zadat apostrof před jakákoli čísla a stisknout „Enter“. Excel ponechá úvodní nuly nedotčené, ale (‘) nebude v listu viditelné, dokud neoznačíte buňku.

Použití funkce Text

Výše uvedená metoda přidává nuly k číslům, když je píšete, ale pokud již máte seznam čísel a chcete před ně dosadit úvodní nuly, pak je pro vás funkce TEXT tou správnou metodou. Funkce TEXT umožňuje převádět čísla na textové řetězce při použití vlastního formátování.

Syntaxe funkce TEXT:

= TEXT( hodnota, formát_text)

Kde,

• hodnota – Je to číselná hodnota, kterou musíte převést na text a použít formátování.
• format_text – je formát, který chcete použít.

Pomocí funkce TEXT můžete určit, kolik číslic má mít vaše číslo. Pokud například chcete, aby vaše čísla měla 8 číslic, zadejte 8 nul do druhého argumentu funkce: „00000000“. Pokud máte v buňce 6místné číslo, pak funkce ručně přidá 2 úvodní nuly a pokud máte 2místná čísla jako 56, zbytek budou nuly (00000056).

Chcete-li například přidat úvodní nuly a vytvořit čísla 6místná, použijte tento vzorec:

=TEXT(A2"000000")

Protože máme ve druhém argumentu vzorce 6 nul, funkce převede číselný řetězec na textový řetězec a přidá 5 úvodních nul, aby byl řetězec dlouhý 6 číslic.

Poznámka: Nezapomeňte prosím ve funkci uzavřít kódy formátu do dvojitých uvozovek.

Nyní můžete použít stejný vzorec na zbytek buněk přetažením úchytu výplně. Jak vidíte, funkce převede čísla na texty a k číslům přidá úvodní nuly tak, aby celkový počet číslic byl 6.

Funkce TEXT vždy vrátí hodnotu jako textový řetězec, nikoli číslo, takže je nebudete moci použít v aritmetických výpočtech, ale stále je můžete použít ve vyhledávacích vzorcích, jako je VLOOKUP nebo INDEX/MATCH k načtení podrobností produkt pomocí ID produktů.

Použití funkce CONCATENATE/operátor Ampersand (&)

Pokud chcete přidat pevný počet úvodních nul před všechna čísla ve sloupci, můžete použít funkci CONCATENATE nebo operátor ampersand (&).

Syntaxe funkce CONCATENATE:

=CONCATENATE(text1; [text2]; ...)

Kde,

text1 – Počet nul, které mají být vloženy před číslo.

text2 – Původní číslo nebo odkaz na buňku

Syntaxe operátoru Ampersand:

=Value_1 & Value_2 

Kde,

Hodnota_1 jsou úvodní nuly, které se mají vložit před číslo, a Hodnota_2 je číslo.

Chcete-li například přidat pouze dvě nuly před číslo, použijte některý z těchto vzorců:

=CONCATENATE("00";A2)

První argument jsou dvě nuly („00“), protože chceme před číslo v A2 (což je druhý argument) doplnit dvě nuly.

Nebo,

="00"&A2

Zde jsou prvním argumentem 2 nuly, za nimi následuje operátor „&“ a druhým argumentem je číslo.

Jak vidíte, vzorec přidá pouze dvě úvodní nuly ke všem číslům ve sloupci bez ohledu na to, kolik číslic číslo obsahuje.

Oba tyto vzorce spojují určitý počet nul před původní čísla a ukládají je jako textové řetězce.

Použití funkce REPT/LEN

Pokud chcete k číselným nebo alfanumerickým datům přidat úvodní nuly a převést řetězec na text, použijte funkci REPT. Funkce REPT se používá k opakování znaku (znaků) určitý počet opakování. Tuto funkci lze také použít k vložení pevných čísel úvodních nul před číslo.

=REPT(text, počet_krát)

Kde ‚text‘ je znak, který chceme opakovat (v našem případě ‚0‘) a argument ‚number_times‘ je počet, kolikrát chceme daný znak opakovat.

Chcete-li například vygenerovat pět nul před čísly, vzorec by vypadal takto:

=REPT(0,5)&A2

Vzorec zopakuje 5 nul a spojí číselný řetězec v A2 a vrátí výsledek. Potom se vzorec použije na buňku B2:B6 pomocí úchytu výplně.

Výše uvedený vzorec přidává před číslo pevný počet nul, ale celková délka čísla se liší v závislosti na čísle.

Pokud chcete přidat úvodní nuly všude tam, kde je potřeba vytvořit řetězce dlouhé (pevné délky) specifického znaku, můžete použít funkce REPT a LEN společně.

Syntax:

=REPT(text, počet_krát-DÉLKA(text))&buňka

Chcete-li například k hodnotě v A2 přidat nuly s předponou a vytvořit řetězec dlouhý 5 znaků, vyzkoušejte tento vzorec:

=REPT(0,5-DÉLKA(A2))&A2

Zde „LEN(A2)“ získá celkovou délku řetězce/čísel v buňce A2. „5“ je maximální délka řetězce/čísel, které by buňka měla mít. A část „REPT(0,5-LEN(A2))“ přidá počet nul odečtením délky řetězce v A2 od maximálního počtu nul (5). Poté se před hodnotu A2 připojí počet 0, aby se vytvořil řetězec pevné délky.

Použití funkce RIGHT

Dalším způsobem, jak vložit úvodní nuly před řetězec v aplikaci Excel, je použití funkce RIGHT.

Funkce RIGHT může přidat na začátek čísla počet nul a extrahovat z hodnoty N znaků nejvíce vpravo.

Syntax:

= RIGHT (text, num_chars)

• text je buňka nebo hodnota, ze které chcete extrahovat znaky.
• počet_znaků je počet znaků, které se mají z textu extrahovat. Pokud tento argument není zadán, bude extrahován pouze první znak.

Pro tuto metodu zřetězujeme maximální počet nul s odkazem na buňku, která obsahuje řetězec v argumentu „text“.

Chcete-li vytvořit 6místné číslo založené na číselném řetězci v A s nulami na začátku, zkuste tento vzorec:

=RIGHT("0000000"&A2;6)

První argument (text) vzorce přidá 7 nul k hodnotě v A2 („0000000“&A2) a poté vrátí 7 znaků úplně vpravo, což vede k několika úvodním nulám.

Přidání úvodních nul pomocí vlastního formátování čísel

Pokud použijete některou z výše uvedených metod k uvedení úvodních nul před čísla, vždy dostanete textový řetězec, nikoli číslo. A nebudou moc užitečné ve výpočtech nebo v numerických vzorcích.

Nejlepší způsob, jak přidat úvodní nuly v Excelu, je použít vlastní formátování čísel. Pokud přidáte úvodní nuly přidáním vlastního číselného formátu do buňky, nezmění se tím hodnota buňky, ale pouze způsob jejího zobrazení. Hodnota stále zůstane jako číslo, nikoli text.

Chcete-li změnit formátování čísel buněk, postupujte takto:

Vyberte buňku nebo oblast buněk, kde chcete zobrazit úvodní nuly. Poté klikněte pravým tlačítkem myši kdekoli ve vybraném rozsahu a z kontextové nabídky vyberte možnost „Formátovat buňky“. Nebo stiskněte klávesové zkratky Ctrl + 1.

V okně Formát buněk přejděte na kartu „Číslo“ a v možnostech kategorie vyberte „Vlastní“.

Zadejte počet nul do pole „Typ:“ a zadejte celkový počet číslic, které chcete zobrazit v buňce. Pokud například chcete, aby číslo mělo 6 číslic, zadejte „000000“ jako kód vlastního formátu. Poté klikněte na „OK“ pro použití.

Před čísly se zobrazí úvodní nuly a pokud je číslo menší než 6 číslic, doplní se před něj nula.

Zdá se, že čísla mají pouze úvodní nuly, zatímco základní hodnota zůstane nezměněna. Pokud vyberete buňku s vlastním formátováním, zobrazí se vám na řádku vzorců původní číslo

Existuje mnoho digitálních zástupných symbolů, které můžete použít ve vlastním formátu čísel. Existují však pouze dva primární zástupné symboly, které můžete použít pro přidání úvodních nul v číslech.

• 0 – Je to zástupný znak číslic, který zobrazuje nuly navíc. Zobrazuje vynucené číslice 0-9 bez ohledu na to, zda tato číslice odpovídá hodnotě. Pokud například zadáte 2.5 s kódem formátu 000.00, zobrazí se 002.50.
• # – Je to zástupný znak číslic, který zobrazuje volitelné číslice a neobsahuje nuly navíc. Pokud například zadáte 123 s formátovacím kódem 000#, zobrazí se 0123.

Také jakékoli interpunkční znaménko nebo jiný znak, který zahrnete do kódu formátu, se zobrazí tak, jak je. Můžete použít znaky jako pomlčku (-), čárku (,), lomítko (/) atd.

Například můžete také formátovat čísla jako telefonní čísla pomocí vlastního formátu.

Dialogové okno Formátovat kód v Formát buněk:

Výsledek:

Aplikujme tento formátovací kód v následujícím příkladu:

##0000

Jak vidíte, „0“ přidá nuly navíc, zatímco „#“ nepřidá bezvýznamné nuly:

Můžete také použít předdefinované kódy formátu v části ‚Speciální formáty‘ v dialogovém okně Formát buněk pro poštovní směrovací čísla, telefonní čísla a čísla sociálního pojištění.

Následující tabulka ukazuje čísla s úvodními nulami, kde jsou na různé sloupce použity různé kódy formátu „Speciální“:

Odstranění úvodních nul v aplikaci Excel

Nyní jste se naučili, jak přidat úvodní nuly v Excelu, podívejme se, jak odstranit úvodní nuly z počtu řetězců. Někdy, když importujete data z externího zdroje, čísla mohou skončit s předponou nul a být formátována jako text. V takových případech musíte odstranit úvodní nuly a převést je zpět na čísla, abyste je mohli použít ve vzorcích.

Existují různé způsoby, jak můžete odstranit úvodní nuly v Excelu a my je uvidíme jednu po druhé.

Odstraňte úvodní nuly změnou formátování buňky

Pokud byly úvodní nuly přidány vlastním formátováním čísel, můžete je snadno odstranit změnou formátu buněk. To, zda jsou vaše buňky naformátované na míru, poznáte podle adresního řádku (nuly budou viditelné v buňce, nikoli v adresním řádku).

Chcete-li odstranit předpony nul, vyberte buňky s úvodními nulami, klikněte na pole ‚Formát čísel‘ a vyberte možnost formátování ‚Obecné‘ nebo ‚Číslo‘.

Nyní jsou úvodní nuly pryč:

Odstraňte úvodní nuly převodem textu na čísla

Pokud byly počáteční nuly přidány změnou formátu buňky nebo přidáním apostrofů před čísla nebo automaticky přidány při importu dat, nejjednodušší způsob, jak je převést na čísla, je pomocí možnosti Kontrola chyb. Postup:

Tuto metodu můžete použít, pokud jsou vaše čísla zarovnána doleva a vaše buňky mají v levém horním rohu buněk malý zelený trojúhelník (indikátor chyby). To znamená, že čísla jsou formátována jako text.

Vyberte tyto buňky a v pravé horní části výběru se zobrazí žluté varování. Poté v rozevíracím seznamu klikněte na možnost „Převést na číslo“.

Vaše nuly budou odstraněny a čísla budou převedena zpět do číselného formátu (zarovnáno vpravo).

Odstranění úvodních nul pomocí Násobit/dělit 1

Dalším snadným a nejlepším způsobem, jak odstranit proklad, je násobení nebo dělení čísel 1. Dělení nebo násobení hodnoty nezmění hodnotu, pouze převede hodnotu zpět na číslo a odstraní úvodní nuly.

Chcete-li to provést, zadejte do buňky vzorec v níže uvedeném příkladu a stiskněte klávesu ENTER. Úvodní nuly budou odstraněny a řetězec bude převeden zpět na číslo.

Potom použijte tento vzorec na další buňky pomocí úchytu výplně.

Stejných výsledků můžete dosáhnout pomocí příkazu ‚Vložit jinak‘. Zde je postup:

Zadejte číselnou hodnotu „1“ do buňky (řekněme do B2) a zkopírujte tuto hodnotu.

Dále vyberte buňky, ve kterých chcete odstranit úvodní nuly. Poté klikněte pravým tlačítkem myši na výběr a vyberte možnost ‚Vložit jinak‘.

V dialogovém okně Vložit jinak v části Operace vyberte možnost ‚Násobit‘ nebo ‚Rozdělit‘ a klikněte na ‚OK‘.

To je vše, vaše úvodní nuly budou odstraněny a řetězce zůstanou jako čísla.

Odstraňte úvodní nuly pomocí vzorců

Dalším snadným způsobem, jak odstranit předpony nul, je použití funkce VALUE. Tato metoda může být užitečná, ať už byly úvodní nuly přidány pomocí jiného vzorce nebo apostrofu nebo pomocí vlastního formátování.

=HODNOTA(A1)

Argumentem vzorce může být hodnota nebo odkaz na buňku, která má hodnotu. Vzorec odstraní úvodní nuly a převede hodnotu z textu na číslo. Poté aplikujte vzorec na zbytek buněk.

Někdy můžete chtít odstranit úvodní nuly, ale chcete zachovat čísla v textovém formátu. V takových případech musíte použít funkce TEXT() a VALUE () společně takto:

=TEXT(HODNOTA(A1),"#")

Funkce VALUE převede hodnotu v A1 na číslo. Ale druhý argument, „#“, převede hodnotu zpět do textového formátu bez dalších nul. V důsledku toho byste získali čísla bez úvodních nul, ale stále v textovém formátu (zarovnáno doleva).

Odstraňte úvodní nuly pomocí funkce Text do sloupců aplikace Excel

Dalším způsobem, jak odstranit úvodní nuly, je použití funkce Text to Columns aplikace Excel.

Vyberte rozsah buněk, které obsahují čísla s nulami na začátku.

Dále přejděte na kartu „Data“ a klikněte na tlačítko „Text do sloupců“ ve skupině Datové nástroje.

Zobrazí se průvodce ‚Převést text na sloupce‘. V kroku 1 ze 3 vyberte „Oddělený“ a klikněte na „Další“.

V kroku 2 ze 3 zrušte zaškrtnutí všech oddělovačů a klikněte na „Další“.

V posledním kroku ponechte možnost Formát dat sloupce na „Obecné“ a vyberte cíl (první buňku rozsahu), kam chcete čísla bez úvodních nul. Poté klikněte na „Dokončit“

A získáte čísla s odstraněným prokladem v samostatném sloupci, jak je uvedeno níže.

To je vše.