Ako pridať úvodné nuly v Exceli

Tento tutoriál obsahuje rôzne metódy na pridávanie alebo ponechanie úvodných núl, ako aj na odstránenie úvodných núl v Exceli.

Kedykoľvek zadáte alebo importujete čísla s jednou alebo viacerými úvodnými nulami, napríklad 000652, Excel tieto nuly automaticky odstráni a v bunkách sa zobrazí iba samotné číslo (652). Je to preto, že úvodné nuly nie sú potrebné na výpočty a nepočítajú sa.

Sú však prípady, keď sú úvodné nuly nevyhnutné, napríklad keď zadávate identifikačné čísla, telefónne čísla, čísla kreditných kariet, kódy produktov alebo poštové smerovacie čísla atď. Našťastie nám Excel ponúka niekoľko spôsobov, ako pridať alebo ponechať úvodné nuly. v bunkách. V tomto článku vám ukážeme rôzne spôsoby, ako pridať alebo ponechať úvodné nuly a odstrániť úvodné nuly.

Pridanie úvodných núl v Exceli

V zásade existujú 2 metódy, ktoré môžete použiť na pridanie úvodných núl: jedna, naformátujte svoje číslo ako „Text“; dva, použite vlastné formátovanie na pridanie úvodných núl. Metóda, ktorú chcete použiť, môže závisieť od toho, čo chcete s číslom urobiť.

Možno budete chcieť pridať úvodnú nulu, keď zadávate jedinečné identifikačné čísla, čísla účtov, čísla sociálneho poistenia alebo PSČ atď. Tieto čísla však nebudete používať na výpočty ani vo funkciách, takže je najlepšie ich previesť čísla na text. Nikdy by ste nespočítali ani nespriemerovali telefónne čísla alebo čísla účtov.

Existuje niekoľko spôsobov, ako môžete pridať alebo doplniť nuly pred čísla tak, že ich naformátujete ako text:

• Zmena formátu bunky na Text
• Pridanie apostrofu (‘)
• Používanie funkcie TEXT
• Používanie funkcie REPT/LEN
• Použite funkciu CONCATENATE/operátor ampersand (&)
• Použitie funkcie RIGHT

Zmena formátu bunky na text

Toto je jeden z najjednoduchších spôsobov, ako k číslam pridať úvodné nuly. Ak sa práve chystáte zadávať čísla a chcete pri písaní ponechať úvodné nuly, potom je táto metóda pre vás. Zmenou formátu bunky zo všeobecného alebo čísla na text môžete prinútiť Excel, aby s vašimi číslami zaobchádzal ako s textovými hodnotami a všetko, čo do bunky zadáte, zostane úplne rovnaké. Urobíte to takto:

Vyberte bunky, do ktorých chcete pridať úvodné nuly. Prejdite na kartu „Domov“, kliknite na rozbaľovacie pole „Formát“ v skupine Čísla a z možností formátu vyberte „Text“.

Teraz, keď zadávate svoje čísla, Excel z nich neodstráni žiadnu úvodnú nulu.

V ľavom hornom rohu bunky môžete vidieť malý zelený trojuholník (indikátor chyby) a keď túto bunku vyberiete, zobrazí sa vám varovný znak označujúci, že ste číslo uložili ako text.

Ak chcete odstrániť chybové hlásenie, vyberte bunku (bunky), kliknite na varovný znak a potom zo zoznamu vyberte možnosť Ignorovať chybu.

Môžete tiež zadať telefónne čísla s medzerou alebo pomlčkou medzi číslami, Excel bude tieto čísla automaticky považovať za text.

Pomocou Leading apostrof ( ' )

Ďalším spôsobom, ako pridať úvodné nuly v Exceli, je pridať apostrof (‘) na začiatok čísla. To prinúti Excel zadať číslo ako text.

Pred ľubovoľné čísla zadajte apostrof a stlačte „Enter“. Excel ponechá úvodné nuly nedotknuté, ale znak (‘) nebude v hárku viditeľný, pokiaľ neoznačíte bunku.

Používanie funkcie Text

Vyššie uvedená metóda pridáva nuly k číslam, keď ich zadávate, ale ak už máte zoznam čísel a chcete pred ne doplniť úvodné nuly, potom je funkcia TEXT pre vás tou správnou metódou. Funkcia TEXT vám umožňuje konvertovať čísla na textové reťazce pri použití vlastného formátovania.

Syntax funkcie TEXT:

= TEXT( hodnota, formát_text)

Kde,

• hodnota - Je to číselná hodnota, ktorú musíte previesť na text a použiť formátovanie.
• format_text – je formát, ktorý chcete použiť.

Pomocou funkcie TEXT môžete určiť, koľko číslic má mať vaše číslo. Napríklad, ak chcete, aby vaše čísla mali 8 číslic, zadajte 8 núl do druhého argumentu funkcie: „00000000“. Ak máte v bunke 6-miestne číslo, funkcia ručne pridá 2 úvodné nuly a ak máte 2-ciferné čísla ako 56, zvyšok budú nuly (00000056).

Napríklad, ak chcete pridať úvodné nuly a urobiť čísla 6-cifernými, použite tento vzorec:

=TEXT(A2,"000000")

Keďže v druhom argumente vzorca máme 6 núl, funkcia skonvertuje číselný reťazec na textový reťazec a pridá 5 úvodných núl, aby bol reťazec dlhý 6 číslic.

Poznámka: Nezabudnite vo funkcii uzavrieť kódy formátu do dvojitých úvodzoviek.

Teraz môžete použiť rovnaký vzorec na zvyšok buniek potiahnutím rukoväte výplne. Ako vidíte, funkcia konvertuje čísla na texty a k číslam pridá úvodné nuly tak, aby celkový počet číslic bol 6.

Funkcia TEXT vždy vráti hodnotu ako textový reťazec, nie číslo, takže ich nebudete môcť použiť v aritmetických výpočtoch, ale stále ich môžete použiť vo vzorcoch na vyhľadávanie, ako sú VLOOKUP alebo INDEX/MATCH na získanie podrobností o produkt pomocou ID produktov.

Používanie funkcie CONCATENATE/operátora Ampersand (&)

Ak chcete pred všetky čísla v stĺpci pridať pevný počet úvodných núl, môžete použiť funkciu CONCATENATE alebo operátor ampersand (&).

Syntax funkcie CONCATENATE:

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

Kde,

text1 – Počet núl, ktoré sa majú vložiť pred číslo.

text2 – Pôvodné číslo alebo odkaz na bunku

Syntax operátora Ampersand:

=Hodnota_1 a Hodnota_2 

Kde,

Hodnota_1 sú úvodné nuly, ktoré sa majú vložiť pred číslo, a Hodnota_2 je číslo.

Napríklad, ak chcete pred číslo pridať iba dve nuly, použite niektorý z týchto vzorcov:

=CONCATENATE("00";A2)

Prvý argument sú dve nuly („00“), pretože pred číslo v A2 (čo je druhý argument) chceme doplniť dve nuly.

alebo

="00"&A2

Tu sú prvým argumentom 2 nuly, za nimi nasleduje operátor „&“ a druhým argumentom je číslo.

Ako vidíte, vzorec pridáva iba dve úvodné nuly ku všetkým číslam v stĺpci bez ohľadu na to, koľko číslic číslo obsahuje.

Oba tieto vzorce spájajú určitý počet núl pred pôvodnými číslami a ukladajú ich ako textové reťazce.

Používanie funkcie REPT/LEN

Ak chcete k číselným alebo alfanumerickým údajom pridať úvodné nuly a previesť reťazec na text, použite funkciu REPT. Funkcia REPT sa používa na opakovanie znaku (znakov) určitý počet krát. Túto funkciu je možné použiť aj na vloženie pevných čísel úvodných núl pred číslo.

=REPT(text, počet_krát)

Kde „text“ je znak, ktorý chceme zopakovať (v našom prípade „0“) a argument „number_times“ je počet, koľkokrát chceme daný znak zopakovať.

Ak chcete napríklad vygenerovať päť núl pred číslami, vzorec bude vyzerať takto:

=REPT(0,5)&A2

Vzorec zopakuje 5 núl a spojí číselný reťazec v A2 a vráti výsledok. Potom sa vzorec použije na bunku B2:B6 pomocou rukoväte výplne.

Vyššie uvedený vzorec pridáva pred číslo pevný počet núl, ale celková dĺžka čísla sa líši v závislosti od čísla.

Ak chcete pridať úvodné nuly všade tam, kde je to potrebné na vytvorenie reťazcov s konkrétnou dĺžkou znakov (pevná dĺžka), môžete použiť funkcie REPT a LEN spolu.

Syntax:

=REPT(text, počet_krát-DĹŽKA(text))&bunka

Ak chcete napríklad k hodnote v A2 pridať nuly s predponou a vytvoriť 5-znakový reťazec, skúste tento vzorec:

=REPT(0,5-LEN(A2))&A2

Tu „LEN(A2)“ získa celkovú dĺžku reťazca/čísel v bunke A2. „5“ je maximálna dĺžka reťazca/čísiel, ktoré by bunka mala mať. A časť „REPT(0,5-LEN(A2))“ pridá počet núl odčítaním dĺžky reťazca v A2 od maximálneho počtu núl (5). Potom sa pred hodnotou A2 spojí počet 0, aby sa vytvoril reťazec s pevnou dĺžkou.

Používanie funkcie RIGHT

Ďalším spôsobom, ako vložiť úvodné nuly pred reťazec v Exceli, je použiť funkciu RIGHT.

Funkcia RIGHT môže pridať na začiatok čísla niekoľko núl a extrahovať z hodnoty N znakov najviac vpravo.

Syntax:

= RIGHT (text, num_chars)

• text je bunka alebo hodnota, z ktorej chcete extrahovať znaky.
• počet_znakov je počet znakov, ktoré sa majú extrahovať z textu. Ak tento argument nie je zadaný, bude extrahovaný iba prvý znak.

Pre túto metódu spájame maximálny počet núl s odkazom na bunku, ktorá obsahuje reťazec v argumente „text“.

Ak chcete vytvoriť 6-miestne číslo na základe číselného reťazca v A s nulami na začiatku, skúste tento vzorec:

=RIGHT("0000000"&A2;6)

Prvý argument (text) vzorca pridá 7 núl k hodnote v A2 („0000000“&A2) a potom vráti 7 znakov úplne vpravo, čo vedie k určitým úvodným nulám.

Pridanie úvodných núl pomocou vlastného formátovania čísel

Ak použijete niektorú z vyššie uvedených metód na uvedenie úvodných núl pred čísla, vždy dostanete textový reťazec, nie číslo. A nebudú veľmi užitočné vo výpočtoch alebo v numerických vzorcoch.

Najlepší spôsob, ako pridať úvodné nuly v Exceli, je použiť vlastné formátovanie čísel. Ak pridáte úvodné nuly pridaním vlastného číselného formátu do bunky, nezmení sa tým hodnota bunky, ale iba spôsob, akým sa zobrazuje. Hodnota zostane ako číslo, nie text.

Ak chcete zmeniť formátovanie čísel buniek, postupujte takto:

Vyberte bunku alebo rozsah buniek, kde chcete zobraziť úvodné nuly. Potom kliknite pravým tlačidlom myši kdekoľvek v rámci zvoleného rozsahu a v kontextovej ponuke vyberte možnosť „Formátovať bunky“. Alebo stlačte klávesové skratky Ctrl + 1.

V okne Formát buniek prejdite na kartu „Číslo“ a v možnostiach kategórie vyberte možnosť „Vlastné“.

Zadajte počet núl do poľa „Typ:“, aby ste určili celkový počet číslic, ktoré chcete zobraziť v bunke. Napríklad, ak chcete, aby číslo malo 6 číslic, zadajte „000000“ ako kód vlastného formátu. Potom kliknite na tlačidlo „OK“ a použite.

Pred číslami sa zobrazia úvodné nuly a ak je číslo menšie ako 6 číslic, doplní sa pred neho nula.

Zdá sa, že čísla majú iba úvodné nuly, zatiaľ čo základná hodnota zostane nezmenená. Ak vyberiete bunku s vlastným formátovaním, zobrazí sa vám pôvodné číslo na riadku vzorcov

Existuje veľa digitálnych zástupných symbolov, ktoré môžete použiť vo svojom vlastnom formáte čísel. Existujú však iba dva hlavné zástupné symboly, ktoré môžete použiť na pridávanie úvodných núl do čísel.

• 0 – Je to zástupný znak číslic, ktorý zobrazuje nuly navyše. Zobrazuje vynútené číslice 0-9 bez ohľadu na to, či číslica súvisí s hodnotou alebo nie. Ak napríklad zadáte 2.5 s kódom formátu 000.00, zobrazí sa 002.50.
• # – Je to zástupný znak číslic, ktorý zobrazuje voliteľné číslice a neobsahuje nuly navyše. Napríklad, ak zadáte 123 s formátovacím kódom 000#, zobrazí sa 0123.

Akékoľvek interpunkčné znamienko alebo iný znak, ktorý zahrniete do kódu formátu, sa zobrazí tak, ako je. Môžete použiť znaky ako spojovník (-), čiarka (,), lomka (/) atď.

Napríklad môžete tiež formátovať čísla ako telefónne čísla pomocou vlastného formátu.

Dialógové okno Formátovať kód v Formát buniek:

Výsledok:

Aplikujme tento formátovací kód v nasledujúcom príklade:

##0000

Ako vidíte, „0“ pridá nuly navyše, zatiaľ čo „#“ nepridá bezvýznamné nuly:

Môžete tiež použiť preddefinované kódy formátu v časti „Špeciálne formáty“ v dialógovom okne Formát buniek pre poštové smerovacie čísla, telefónne čísla a čísla sociálneho poistenia.

Nasledujúca tabuľka zobrazuje čísla s nulami na začiatku, kde sa v rôznych stĺpcoch používajú rôzne kódy „špeciálneho“ formátu:

Odstránenie úvodných núl v Exceli

Teraz ste sa naučili, ako pridať úvodné nuly v Exceli, pozrime sa, ako odstrániť úvodné nuly z počtu reťazcov. Niekedy, keď importujete údaje z externého zdroja, čísla môžu mať predponu nuly a môžu byť formátované ako text. V takýchto prípadoch musíte odstrániť úvodné nuly a previesť ich späť na čísla, aby ste ich mohli použiť vo vzorcoch.

Existujú rôzne spôsoby, ako môžete odstrániť úvodné nuly v Exceli a uvidíme ich jednu po druhej.

Odstráňte úvodné nuly zmenou formátovania bunky

Ak boli úvodné nuly pridané vlastným formátovaním čísel, môžete ich jednoducho odstrániť zmenou formátu buniek. Či sú vaše bunky naformátované na mieru, zistíte tak, že sa pozriete na panel s adresou (nuly budú viditeľné v bunke, nie v paneli s adresou).

Ak chcete odstrániť predpony núl, vyberte bunky s úvodnými nulami, kliknite na pole „Formát čísel“ a vyberte možnosť formátovania „Všeobecné“ alebo „Číslo“.

Teraz sú prvé nuly preč:

Odstráňte úvodné nuly prevedením textu na čísla

Ak boli počiatočné nuly pridané zmenou formátu bunky alebo pridaním apostrofov pred čísla alebo automaticky pridaných pri importovaní údajov, najjednoduchší spôsob, ako ich previesť na čísla, je použiť možnosť kontroly chýb. Urobíte to takto:

Túto metódu môžete použiť, ak sú vaše čísla zarovnané doľava a vaše bunky majú malý zelený trojuholník (indikátor chyby) v ľavom hornom rohu buniek. To znamená, že čísla sú formátované ako text.

Vyberte tieto bunky a v pravej hornej časti výberu sa zobrazí žlté upozornenie. Potom v rozbaľovacej ponuke kliknite na možnosť „Previesť na číslo“.

Vaše nuly sa odstránia a čísla sa skonvertujú späť do číselného formátu (zarovnané doprava).

Odstránenie úvodných núl pomocou Násobenie/delenie 1

Ďalším jednoduchým a najlepším spôsobom, ako odstrániť úvod, je násobenie alebo delenie čísel 1. Delenie alebo násobenie hodnoty nezmení hodnotu, jednoducho prevedie hodnotu späť na číslo a odstráni úvodné nuly.

Ak to chcete urobiť, zadajte do bunky vzorec z nižšie uvedeného príkladu a stlačte kláves ENTER. Počiatočné nuly sa odstránia a reťazec sa skonvertuje späť na číslo.

Potom použite tento vzorec na ďalšie bunky pomocou rukoväte výplne.

Rovnaké výsledky môžete dosiahnuť pomocou príkazu „Prilepiť špeciálne“. Tu je postup:

Zadajte číselnú hodnotu „1“ do bunky (povedzme do B2) a skopírujte túto hodnotu.

Ďalej vyberte bunky, v ktorých chcete odstrániť úvodné nuly. Potom kliknite pravým tlačidlom myši na výber a vyberte možnosť „Prilepiť špeciálne“.

V dialógovom okne Prilepiť špeciálne v časti Operácia vyberte možnosť „Vynásobiť“ alebo „Rozdeliť“ a kliknite na tlačidlo „OK“.

To je všetko, vaše úvodné nuly sa odstránia a reťazce zostanú čísla.

Odstráňte úvodné nuly pomocou vzorcov

Ďalším jednoduchým spôsobom vymazania núl s predponou je použitie funkcie VALUE. Táto metóda môže byť užitočná bez ohľadu na to, či boli úvodné nuly pridané pomocou iného vzorca alebo apostrofu alebo pomocou vlastného formátovania.

=HODNOTA(A1)

Argumentom vzorca môže byť hodnota alebo odkaz na bunku, ktorá má hodnotu. Vzorec odstráni úvodné nuly a prevedie hodnotu z textu na číslo. Potom aplikujte vzorec na zvyšok buniek.

Niekedy možno budete chcieť odstrániť úvodné nuly, ale chcete ponechať čísla v textovom formáte. V takýchto prípadoch musíte použiť funkcie TEXT() a VALUE () spolu takto:

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

Funkcia VALUE konvertuje hodnotu v A1 na číslo. Ale druhý argument „#“ prevedie hodnotu späť do textového formátu bez akýchkoľvek núl navyše. V dôsledku toho by ste dostali čísla bez úvodných núl, ale stále v textovom formáte (zarovnané doľava).

Odstráňte úvodné nuly pomocou funkcie Text do stĺpcov v Exceli

Ďalším spôsobom, ako odstrániť úvodné nuly, je použiť funkciu Text do stĺpcov v Exceli.

Vyberte rozsah buniek, ktoré obsahujú čísla s nulami na začiatku.

Ďalej prejdite na kartu „Údaje“ a kliknite na tlačidlo „Text do stĺpcov“ v skupine Nástroje údajov.

Zobrazí sa sprievodca „Previesť text na stĺpce“. V kroku 1 z 3 vyberte „Oddelené“ a kliknite na „Ďalej“.

V kroku 2 z 3 zrušte začiarknutie všetkých oddeľovačov a kliknite na tlačidlo „Ďalej“.

V poslednom kroku ponechajte možnosť Formát údajov stĺpca ako „Všeobecné“ a vyberte cieľ (prvá bunka rozsahu), kde chcete mať čísla bez úvodných núl. Potom kliknite na „Dokončiť“

A dostanete čísla s odstráneným úvodom v samostatnom stĺpci, ako je uvedené nižšie.

To je všetko.