Ako odstrániť úvodné nuly v Exceli [5 jednoduchých spôsobov]

Mnoho ľudí má vzťah lásky a nenávisti k vodiacim nulám v Exceli.Niekedy to chceš, niekedy nie.

Aj keď je Excel naprogramovaný tak, aby z čísel automaticky odstraňoval všetky úvodné nuly, existujú určité situácie, kedy sa s týmito situáciami môžete stretnúť.

V tomto návode na Excel vám to ukážemako to jeOdstráňte úvodné nuly z čísel v Exceli.

Tak poďme na to!

Možné príčiny úvodných núl v Exceli

Ako som už spomenul, Excel z čísel automaticky odstráni všetky úvodné nuly.Ak napríklad zadáte 00100 do bunky v Exceli, automaticky sa prevedie na 100.

Vo väčšine prípadov to dáva zmysel, pretože tie vedúce nuly v skutočnosti nedávajú zmysel.

Ale v niektorých prípadoch to možno budete potrebovať.

Tu je niekoľko dôvodov, prečo si vaše čísla môžu ponechať úvodné nuly:

• Ak bolo číslo naformátované ako text (hlavne pridaním apostrofu pred číslo), zachovajú sa úvodné nuly.
• Bunky mohli byť naformátované tak, aby vždy zobrazovali čísla určitej dĺžky.Ak je číslo menšie, pridajú sa úvodné nuly, aby sa to vykompenzovalo.Môžete napríklad naformátovať bunku tak, aby vždy zobrazovala 5 číslic (ak je číslo menšie ako 5 číslic, automaticky sa pridajú úvodné nuly)

Spôsob, ktorý zvolíme na odstránenie úvodných núl, závisí od toho, čo to spôsobuje.

Prvým krokom je teda identifikovať príčinu, aby sme mohli zvoliť správny spôsob odstránenia týchto vedúcich núl.

Ako odstrániť úvodné nuly z čísel

Úvodné nuly z čísel môžete odstrániť niekoľkými spôsobmi.

V tejto časti vám ukážem päť takýchto metód.

Prevod textu na čísla s možnosťami kontroly chýb

Ak je dôvodom vedúcich čísel to, že niekto pred ne pridal apostrof (prevod týchto čísel na text), pomocou kontroly chýb môžete tieto čísla previesť späť na čísla jediným kliknutím.

Toto je pravdepodobne najjednoduchší spôsob, ako sa zbaviť vedúcich núl.

Tu mám súbor údajov s číslami s apostrofom pred týmito číslami a úvodnými nulami.Preto vidíte, že čísla sú zarovnané doľava (zatiaľ čo predvolené čísla sú zarovnané doprava) a tiež majú nuly na začiatku.

Tu sú kroky na odstránenie týchto úvodných núl z týchto čísel:

1. Vyberte číslo, z ktorého chcete odstrániť úvodné nuly.V pravom hornom rohu výberu si všimnete žltú ikonu.
2. Kliknite na žltú ikonu kontroly chýb
3. Kliknite na „Previesť na číslo“

To je všetko!Vyššie uvedené kroky budúodstrániť apostrofa previesť tieto textové hodnoty späť na čísla.

A keďže Excel je naprogramovaný tak, aby v predvolenom nastavení odstraňoval medzery na začiatku z akéhokoľvek čísla, uvidíte, že sa tým automaticky odstránia všetky úvodné nuly.

Poznámka: Táto metóda nebude fungovať, ak boli úvodné nuly pridané ako súčasť vlastného číselného formátu bunky.Na zvládnutie týchto situácií použite metódy popísané nižšie.

Zmena vlastného číselného formátu bunky

Ďalším skutočne bežným dôvodom, prečo sa vo vašich číslach môžu zobrazovať nuly na začiatku, je, že vaše bunky boli naformátované tak, aby v každom čísle vždy zobrazovali určitý počet čísel.

Mnoho ľudí chce, aby čísla vyzerali konzistentne a mali rovnakú dĺžku, a preto určujú minimálnu dĺžku čísel zmenou formátu buniek.

Napríklad, ak chcete, aby sa všetky čísla zobrazovali ako 5-miestne, ak máte číslo iba s tromi číslicami, Excel k nemu automaticky pridá dve úvodné nuly.

Nižšie mám množinu údajov s vlastným formátom čísel, ktorý sa používa na zobrazenie aspoň piatich číslic v bunke.

Spôsob, ako sa zbaviť týchto úvodných núl, je jednoducho odstrániť existujúce formátovanie z bunky.

Tu sú kroky, ako to urobiť:

1. Vyberte bunky s číslami úvodnej nuly
2. Kliknite na kartu Domov
3. V skupine Čísla kliknite na rozbaľovaciu ponuku Formát čísel
4. Vyberte "Všeobecné"

Vyššie uvedené kroky zmenia vlastný formát čísel bunky a teraz sa čísla zobrazia podľa očakávania (bez úvodných núl).

Upozorňujeme, že táto technika funguje iba vtedy, ak je dôvodom úvodnej nuly vlastné formátovanie čísla.Nebude to fungovať, ak použijete apostrofy na prevod čísel na text (v tomto prípade by ste mali použiť predchádzajúcu metódu)

Vynásobte 1 (pomocou špeciálnej techniky vkladania)

Táto technika funguje v oboch prípadoch (ak bolo číslo prevedené na text pomocou apostrofov alebo ak bol na bunku použitý vlastný formát čísla).

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete z nej odstrániť úvodné nuly.

Tu sú kroky, ako to urobiť

1. Skopírujte všetky prázdne bunky v hárku
2. Vyberte bunky s číslami, z ktorých chcete odstrániť úvodné nuly
3. Kliknite pravým tlačidlom myši na výber a kliknite na položku Prilepiť špeciálne.Tým sa otvorí dialógové okno Prilepiť špeciálne
4. Kliknite na možnosť Pridať (v skupine Akcie)
5. Kliknite na tlačidlo OK

Vyššie uvedené kroky pridajú 0 do vybratého rozsahu buniek a odstránia všetky úvodné nuly a apostrofy.

Aj keď sa tým nezmení hodnota bunky, skonvertujú sa všetky textové hodnoty na čísla a skopíruje sa formátovanie z prázdnej bunky, ktorú ste skopírovali (čím sa nahradí existujúce formátovanie, pri ktorom sa objavia úvodné nuly).

Táto metóda ovplyvňuje iba čísla.Ak je v bunke nejaký textový reťazec, zostane nezmenený.

Použite funkciu HODNOTA

Ďalším rýchlym a jednoduchým spôsobom, ako odstrániť úvodné nuly, je použiť funkciu hodnoty.

Táto funkcia prevezme jeden argument (ktorým môže byť text alebo odkaz na bunku s textom) a vráti číselnú hodnotu.

To platí aj pre oba prípady, keď je prvé číslo apostrof (používa sa na prevod čísel na text) alebo je výsledkom vlastného číselného formátu.

Predpokladajme, že mám súbor údajov, ktorý vyzerá takto:

Tu je vzorec na odstránenie úvodných núl:

=HODNOTA(A1)

Poznámka: Ak stále vidíte počiatočné nuly, musíte prejsť na kartu Domov a zmeniť formát bunky na Všeobecné (z rozbaľovacej ponuky Formát čísla).

Použite Text do stĺpcov

Zatiaľ čo funkcia Text to Columns sa používa na rozdelenie buniek do viacerých stĺpcov, môžete ju použiť aj na odstránenie úvodných núl.

Predpokladajme, že máte množinu údajov, ktorá vyzerá takto:

Tu sú kroky na odstránenie úvodných núl pomocou Text to Columns:

1. Vyberte rozsah buniek s číslami
2. Kliknite na kartu Údaje
3. V skupine Nástroje údajov kliknite na položku Text do stĺpcov
4. V sprievodcovi prevodom textu na stĺpce vykonajte nasledujúce zmeny:
   1. Krok 1 z 3: Vyberte „Oddeliť“ a kliknite na „Ďalej“
   2. Krok 2 z 3: Zrušte výber všetkých oddeľovačov a kliknite na tlačidlo Ďalej
   3. Krok 3 z 3: Vyberte cieľovú bunku (v tomto prípade B2) a kliknite na tlačidlo Dokončiť

Vyššie uvedené kroky by mali odstrániť všetky úvodné nuly a poskytnúť iba čísla.Ak stále vidíte počiatočné nuly, musíte zmeniť formát bunky na Všeobecné (toto je možné vykonať na karte Domov)

Ako odstrániť úvodné nuly z textu

Zatiaľ čo všetky vyššie uvedené metódy fungujú dobre, tieto metódy fungujú len pre tie bunky, ktoré majú číselné hodnoty.

Čo však robiť, ak vaše alfanumerické alebo textové hodnoty majú tiež nejaké počiatočné nuly.

V tomto prípade vyššie uvedená metóda nebude fungovať, ale vďaka úžasným vzorcom v Exceli stále môžete získať čas.

Predpokladajme, že máte množinu údajov, ako je táto, a chcete z nej odstrániť všetky úvodné nuly:

Tu je vzorec, ako to urobiť:

=RIGHT(A2,LEN(A2)-FIND(LEFT(SUBSTITUTE(A2,"0",""),1),A2)+1)

Dovoľte mi vysvetliť, ako tento vzorec funguje

Časť vzorca SUBSTITUTE nahrádza nuly medzerami.Takže pre hodnotu 001AN76 dáva náhradný vzorec výsledok 1AN76

Vzorec LEFT potom extrahuje znak úplne vľavo z tohto výsledného reťazca, ktorým je v tomto prípade 1.

Vzorec NÁJSŤ potom nájde znak úplne vľavo daný vzorcom VĽAVO a vráti jeho pozíciu.V našom príklade pre hodnotu 001AN76 by to dalo 3 (to znamená pozícia 1 v pôvodnom textovom reťazci).

1 sa pridá k výsledku vzorca FIND, aby sa zabezpečilo, že sme extrahovali celý textový reťazec (okrem úvodných núl)

Potom odčítajte výsledok vzorca FIND od výsledku vzorca LEN, čím získate dĺžku celého textového reťazca.To nám dáva dĺžku textovej slučky bez úvodných núl.

Táto hodnota sa potom použije s funkciou RIGHT na extrahovanie celého textového reťazca (okrem úvodných núl).

Ak máte v bunkách medzery na začiatku alebo na konci, najlepšie je použiť funkciu TRIM pre každú referenciu na bunku.

Preto nový vzorec s pridaním funkcie TRIM vyzerá takto:

=RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND(LEFT(SUBSTITUTE(TRIM(A2),"0",""),1),TRIM(A2))+1)

Preto môžete použiť tieto jednoduché metódy na odstránenie úvodných núl z množín údajov v Exceli.

Dúfam, že tento návod bol pre vás užitočný!