Kako dodati vodeće nule u Excel

Ovaj vodič pokriva različite metode dodavanja ili zadržavanja vodećih nula, kao i uklanjanja vodećih nula u Excelu.

Kad god unesete ili uvezete brojeve s jednom ili više vodećih nula, kao što je 000652, Excel automatski uklanja te nule, a samo se broj prikazuje u ćelijama (652). To je zato što vodeće nule nisu potrebne za izračune i ne računaju se.

Međutim, postoje slučajevi kada su te vodeće nule potrebne, na primjer kada unosite ID brojeve, telefonske brojeve, brojeve kreditnih kartica, kodove proizvoda ili poštanskih brojeva, itd. Srećom, Excel nam daje nekoliko načina za dodavanje ili zadržavanje vodećih nula u stanicama. U ovom članku ćemo vam pokazati različite načine dodavanja ili zadržavanja vodećih nula i uklanjanja vodećih nula.

Dodavanje vodećih nula u Excel

U osnovi, postoje 2 metode koje možete koristiti za dodavanje vodećih nula: jedan, formatirajte svoj broj kao 'Tekst'; drugo, koristite prilagođeno oblikovanje za dodavanje vodećih nula. Metoda koju želite koristiti može ovisiti o tome što želite učiniti s brojem.

Možda ćete htjeti dodati vodeću nulu kada unosite jedinstvene ID brojeve, brojeve računa, brojeve socijalnog osiguranja ili poštanske brojeve, itd. Ali, nećete koristiti te brojeve za izračune ili u funkcijama, pa je najbolje da ih pretvorite brojeva u tekst. Nikada ne biste zbrajali ili prosječili telefonske brojeve ili brojeve računa.

Postoji nekoliko načina na koje možete dodati ili dodati nule ispred brojeva tako da ih formatirate kao tekst:

  • Promjena formata ćelije u Tekst
  • Dodavanje apostrofa (‘)
  • Korištenje funkcije TEXT
  • Korištenje funkcije REPT/LEN
  • Upotrijebite funkciju CONCATENATE/ampersand operator (&)
  • Korištenje funkcije DESNO

Promjena formata ćelije u tekst

Ovo je jedan od najjednostavnijih načina da svojim brojevima dodate vodeće nule. Ako ćete samo unositi brojeve i želite zadržati vodeće nule dok upisujete, onda je ovo metoda za vas. Promjenom formata ćelije iz Općenito ili Broj u tekstualni, možete prisiliti Excel da tretira vaše brojeve kao tekstualne vrijednosti i sve što upišete u ćeliju ostat će potpuno isto. Evo kako to radite:

Odaberite ćeliju(e) u koje želite dodati vodeće nule. Idite na karticu "Početna", kliknite na padajući okvir "Format" u grupi Brojevi i odaberite "Tekst" iz opcija formata.

Sada kada upišete svoje brojeve, Excel neće izbrisati nijednu vodeću nulu iz njega.

Možda ćete vidjeti mali zeleni trokut (indikator pogreške) u gornjem lijevom kutu ćelije i kada odaberete tu ćeliju, prikazat će vam znak upozorenja koji pokazuje da ste pohranili broj kao tekst.

Da biste uklonili poruku o pogrešci, odaberite ćeliju(e), kliknite na znak upozorenja, a zatim s popisa odaberite "Ignore Error".

Također možete upisivati ​​telefonske brojeve s razmakom ili crticom između brojeva, Excel će te brojeve automatski tretirati kao tekst.

Korištenje Leading Apostrof (')

Drugi način dodavanja vodećih nula u Excel je dodavanje apostrofa (‘) na početak broja. To će prisiliti Excel da unese broj kao tekst.

Samo upišite apostrof ispred bilo kojeg broja i pritisnite "Enter". Excel će ostaviti vodeće nule netaknute, ali (‘) neće biti vidljiv na radnom listu osim ako ne odaberete ćeliju.

Korištenje funkcije teksta

Gornja metoda dodaje nule brojevima dok ih upisujete, ali ako već imate popis brojeva i želite staviti vodeće nule ispred njih, onda je funkcija TEXT prava metoda za vas. Funkcija TEXT omogućuje vam pretvaranje brojeva u tekstualne nizove uz primjenu prilagođenog oblikovanja.

Sintaksa funkcije TEXT:

= TEKST (vrijednost, format_tekst)

Gdje,

  • vrijednost - To je brojčana vrijednost koju trebate pretvoriti u tekst i primijeniti oblikovanje.
  • format_tekst – je format koji želite primijeniti.

Pomoću funkcije TEXT možete odrediti koliko znamenki vaš broj treba imati. Na primjer, ako želite da vaši brojevi budu dugi 8 znamenki, upišite 8 nula u drugi argument funkcije: "00000000". Ako imate 6-znamenkasti broj u ćeliji, tada će funkcija ručno dodati 2 vodeće nule, a ako imate dvoznamenkaste brojeve kao što je 56, ostatak će biti nule (00000056).

Na primjer, da biste dodali vodeće nule i učinili brojeve 6-znamenkastim dugima, koristite ovu formulu:

=TEXT(A2,"000000")

Budući da u drugom argumentu formule imamo 6 nula, funkcija pretvara brojčani niz u tekstualni niz i dodaje 5 vodećih nula kako bi niz bio dug 6 znamenki.

Bilješka: Ne zaboravite priložiti kodove formata u dvostrukim navodnicima u funkciji.

Sada možete primijeniti istu formulu na ostale ćelije povlačenjem ručke za punjenje. Kao što vidite, funkcija pretvara brojeve u tekst i dodaje nule na početku brojevima tako da ukupan broj znamenki bude 6.

Funkcija TEXT uvijek će vratiti vrijednost kao tekstualni niz, a ne kao broj, tako da ih nećete moći koristiti u aritmetičkim izračunima, ali ih i dalje možete koristiti u formulama za pretraživanje kao što su VLOOKUP ili INDEX/MATCH za dohvaćanje pojedinosti proizvoda pomoću ID-ova proizvoda.

Upotreba funkcije CONCATENATE/operator ampersanda (&)

Ako želite dodati fiksni broj vodećih nula ispred svih brojeva u stupcu, možete koristiti funkciju CONCATENATE ili ampersand operator (&).

Sintaksa funkcije CONCATENATE:

=KONKATENATI(tekst1, [tekst2], ...)

Gdje,

tekst 1 – Broj nula koje treba umetnuti ispred broja.

tekst 2 – Izvorni broj ili referenca ćelije

Sintaksa operatora Ampersand:

=Vrijednost_1 i Vrijednost_2

Gdje,

Vrijednost_1 je vodeće nule za umetanje ispred broja, a Vrijednost_2 je broj.

Na primjer, da biste dodali samo dvije nule ispred broja, koristite bilo koju od ove formule:

=KONKATENIRAJ("00",A2)

Prvi argument su dvije nule (“00”) jer želimo dodati dvije nule ispred broja u A2 (koji je drugi argument).

Ili, 

="00"&A2

Ovdje su prvi argument 2 nule, nakon čega slijedi operator '&', a drugi argument je broj.

Kao što možete vidjeti, formula dodaje samo dvije vodeće nule svim brojevima u stupcu bez obzira na to koliko znamenki broj sadrži.

Obje ove formule spajaju određeni broj nula prije izvornih brojeva i spremaju ih kao tekstualne nizove.

Korištenje funkcije REPT/LEN

Ako numeričkim ili alfanumeričkim podacima želite dodati vodeće nule i pretvoriti niz u tekst, upotrijebite funkciju REPT. Funkcija REPT koristi se za ponavljanje znakova određeni broj puta. Ova se funkcija također može koristiti za umetanje fiksnih brojeva vodećih nula ispred broja.

=REPT(tekst, broj_puta)

Gdje je ‘text’ znak koji želimo ponoviti (u našem slučaju ‘0’), a argument ‘number_times’ je koliko puta želimo ponoviti taj znak.

Na primjer, da biste generirali pet nula ispred brojeva, formula bi izgledala ovako:

=REPT(0,5)&A2

Formula ponavlja 5 nula i spaja niz brojeva u A2 i vraća rezultat. Zatim se formula primjenjuje na ćeliju B2:B6 pomoću ručke za popunjavanje.

Gornja formula dodaje fiksni broj nula ispred broja, ali ukupna duljina broja varira ovisno o broju.

Ako želite dodati vodeće nule gdje god je to potrebno za stvaranje nizova dugih specifičnih znakova (fiksne duljine), možete zajedno koristiti funkcije REPT i LEN.

Sintaksa:

=REPT(tekst, broj_puta-LEN(tekst))&ćelija

Na primjer, da biste vrijednosti u A2 dodali nule s prefiksom i napravili niz od 5 znakova, pokušajte ovu formulu:

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

Ovdje 'LEN(A2)' dobiva ukupnu duljinu niza/brojeva u ćeliji A2. '5' je maksimalna duljina niza/brojeva koje ćelija treba imati. A dio ‘REPT(0,5-LEN(A2))’ dodaje broj nula oduzimanjem duljine niza u A2 od maksimalnog broja nula (5). Zatim se broj 0 spaja prije vrijednosti A2 kako bi se napravio niz fiksne duljine.

Korištenje funkcije DESNO

Drugi način umetanja vodećih nula ispred niza u Excelu je korištenje funkcije DESNO.

Funkcija DESNO može dodati broj nula na početak broja i izdvojiti krajnjih N znakova iz vrijednosti.

Sintaksa:

= DESNO (tekst, broj_znakova)
  • tekst je ćelija ili vrijednost iz koje želite izdvojiti znakove.
  • broj_znakova je broj znakova koje treba izdvojiti iz teksta. Ako ovaj argument nije dan, tada će se izdvojiti samo prvi znak.

Za ovu metodu spajamo maksimalni broj nula s referencom ćelije koja sadrži niz u argumentu 'tekst'.

Da biste stvorili 6-znamenkasti broj na temelju niza brojeva u A s vodećim nulama, pokušajte ovu formulu:

=DESNO("0000000"&A2,6)

Prvi argument (tekst) formule dodaje 7 nula vrijednosti u A2 (“0000000”&A2), a zatim vraća krajnjih 7 znakova, što rezultira nekim vodećim nulama.

Dodavanje vodećih nula pomoću prilagođenog oblikovanja brojeva

Ako koristite bilo koju od gore navedenih metoda da stavite vodeće nule ispred brojeva, uvijek ćete dobiti tekstualni niz, a ne broj. I neće biti od velike koristi u izračunima ili u brojčanim formulama.

Najbolji način za dodavanje vodećih nula u Excel je primjena prilagođenog oblikovanja brojeva. Ako dodate vodeće nule dodavanjem prilagođenog formata broja u ćeliju, to ne mijenja vrijednost ćelije, već samo način na koji je prikazana. Vrijednost će i dalje ostati broj, a ne tekst.

Da biste promijenili formatiranje brojeva ćelija, slijedite ove korake:

Odaberite ćeliju ili raspon ćelija u kojima želite prikazati vodeće nule. Zatim desnom tipkom miša kliknite bilo gdje unutar odabranog raspona i odaberite opciju 'Format Cells' iz kontekstnog izbornika. Ili pritisnite tipke prečaca Ctrl + 1.

U prozoru Format Cells idite na karticu "Broj" i odaberite "Prilagođeno" ispod opcija kategorije.

Unesite broj nula u okvir "Vrsta:" da biste odredili ukupan broj znamenki koje želite prikazati u ćeliji. Na primjer, ako želite da broj ima 6 znamenki, unesite '000000' kao kod prilagođenog formata. Zatim kliknite "U redu" za primjenu.

Ovo će pokazati vodeće nule ispred brojeva, a ako je broj manji od 6 znamenki, stavlja nulu ispred.

Čini se da brojevi imaju samo vodeće nule, dok će temeljna vrijednost ostati nepromijenjena. Ako odaberete ćeliju s prilagođenim oblikovanjem, prikazat će vam izvorni broj u traci formule

Postoji mnogo digitalnih rezerviranih mjesta koje možete koristiti u svom prilagođenom formatu brojeva. Ali postoje samo dva primarna rezervirana mjesta koja možete koristiti za dodavanje vodećih nula u brojeve.

  • 0 – To je oznaka mjesta koja prikazuje dodatne nule. Prikazuje prisilne znamenke 0-9 bez obzira na to je li znamenka relevantna za vrijednost ili ne. Na primjer, ako upišete 2.5 s kodom formata 000.00, prikazat će se 002.50.
  • # – To je rezervirano mjesto znamenki koje prikazuje neobavezne znamenke i ne uključuje dodatne nule. Na primjer, ako upišete 123 s kodom oblikovanja 000#, prikazat će se 0123.

Također, bilo koji interpunkcijski znak ili drugi znak koji uključite u kod formata bit će prikazan takav kakav jest. Možete koristiti znakove kao što su crtica (-), zarez (,), kosa crta (/) itd.

Na primjer, također možete formatirati brojeve kao telefonske brojeve korištenjem prilagođenog formata.

Oblikovanje koda u dijaloškom okviru Oblikovanje ćelija:

Rezultat:

Primijenimo ovaj kod za oblikovanje u sljedećem primjeru:

##0000

Kao što možete vidjeti, '0' će dodati dodatne nule dok '#' ne dodaje beznačajne nule:

Također možete koristiti unaprijed definirane kodove formata u odjeljku "Posebni formati" dijaloškog okvira Oblikovanje ćelija za poštanske brojeve, telefonske brojeve i brojeve socijalnog osiguranja.

Sljedeća tablica prikazuje brojeve s vodećim nulama gdje se različiti kodovi "posebnog" formata primjenjuju na različite stupce:

Uklanjanje vodećih nula u Excelu

Sada, naučili ste kako dodati vodeće nule u Excelu, pogledajmo kako ukloniti vodeće nule iz broja nizova. Ponekad, kada uvezete podatke iz vanjskog izvora, brojevi mogu na kraju imati prefiks nula i biti oblikovani kao tekst. U takvim slučajevima morate ukloniti vodeće nule i pretvoriti ih natrag u brojeve kako biste ih mogli koristiti u formulama.

Postoje različiti načini na koje možete ukloniti vodeće nule u Excelu, a mi ćemo ih vidjeti jednu po jednu.

Uklonite vodeće nule promjenom formatiranja ćelije

Ako su vodeće nule dodane prilagođenim oblikovanjem brojeva, možete ih jednostavno ukloniti promjenom formata ćelija. Možete utvrditi jesu li vaše ćelije prilagođene formatu gledajući adresnu traku (nule će biti vidljive u ćeliji, a ne u adresnoj traci).

Da biste uklonili nule s prefiksom, odaberite ćelije s vodećim nulama, kliknite okvir 'Format broja' i odaberite opciju oblikovanja 'Općenito' ili 'Broj'.

Sada, vodeće nule su nestale:

Izbrišite vodeće nule pretvaranjem teksta u brojeve

Ako su vaše vodeće nule dodane promjenom formata ćelije ili dodavanjem apostrofa ispred brojeva ili automatski dodane prilikom uvoza podataka, najlakši način da ih pretvorite u brojeve je korištenje opcije provjere pogreške. Evo kako to radite:

Ovu metodu možete koristiti ako su vaši brojevi poravnati lijevo i vaše ćelije imaju mali zeleni trokut (indikator pogreške) u gornjem lijevom kutu ćelija. To znači da su brojevi oblikovani kao tekst.

Odaberite te ćelije i vidjet ćete žuto upozorenje u gornjem desnom dijelu odabira. Zatim kliknite opciju "Pretvori u broj" na padajućem izborniku.

Vaše će nule biti uklonjene, a brojevi će biti pretvoreni natrag u format brojeva (poravnani udesno).

Uklanjanje vodećih nula po Pomnožite/dijelite s 1

Još jedan jednostavan i najbolji način za uklanjanje vodećih je množenjem ili dijeljenjem brojeva s 1. Dijeljenjem ili množenjem vrijednosti se vrijednost ne mijenja, već se vrijednost jednostavno pretvara u broj i uklanja vodeće nule.

Da biste to učinili, upišite formulu u donjem primjeru u ćeliju i pritisnite ENTER. Vodeće nule bit će uklonjene, a niz će se pretvoriti natrag u broj.

Zatim primijenite ovu formulu na druge ćelije pomoću ručke za popunjavanje.

Iste rezultate možete postići korištenjem naredbe "Specijalno zalijepi". Evo kako:

Upišite brojčanu vrijednost '1' u ćeliju (recimo u B2) i kopirajte tu vrijednost.

Zatim odaberite ćelije u kojima želite ukloniti vodeće nule. Zatim desnom tipkom miša kliknite odabir, a zatim odaberite opciju "Posebno zalijepi".

U dijaloškom okviru Posebno lijepljenje, u odjeljku Operacija, odaberite opciju "Množenje" ili "Podijeli" i kliknite "U redu".

To je to, vaše će početne nule biti uklonjene, a nizovi ostaju brojevi.

Uklonite vodeće nule pomoću formula

Još jedan jednostavan način za brisanje nula s prefiksom je korištenje funkcije VRIJEDNOST. Ova metoda može biti korisna bez obzira na to jesu li vaše vodeće nule dodane upotrebom druge formule ili apostrofa ili prilagođenim oblikovanjem.

=VRIJEDNOST(A1)

Argument formule može biti vrijednost ili referenca ćelije koja ima vrijednost. Formula uklanja vodeće nule i pretvara vrijednost iz teksta u broj. Zatim primijenite formulu na ostale stanice.

Ponekad ćete možda htjeti ukloniti vodeće nule, ali želite zadržati brojeve u tekstualnom formatu. U takvim slučajevima morate koristiti funkcije TEXT() i VALUE () zajedno na sljedeći način:

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

Funkcija VALUE pretvara vrijednost u A1 u broj. Ali drugi argument, '#' pretvara vrijednost natrag u tekstualni format bez dodatnih nula. Kao rezultat toga, dobili biste brojeve bez vodećih nula, ali i dalje u tekstualnom formatu (poravnano lijevo).

Uklonite vodeće nule pomoću Excelove značajke Tekst u stupce

Još jedan način uklanjanja vodećih nula je korištenje Excelove značajke Text to Columns.

Odaberite raspon ćelija koje imaju brojeve s vodećim nulama.

Zatim idite na karticu "Podaci" i kliknite na gumb "Tekst u stupce" u grupi Alati za podatke.

Pojavit će se čarobnjak "Pretvori tekst u stupce". U koraku 1 od 3 odaberite "Delimited" i kliknite "Dalje".

U koraku 2 od 3 poništite sve graničnike i kliknite "Dalje".

U posljednjem koraku ostavite opciju Format podataka stupca kao "Općenito" i odaberite odredište (prva ćelija raspona) gdje želite svoje brojeve bez vodećih nula. Zatim kliknite "Završi"

I dobit ćete brojeve s uklonjenim vodećim u zasebnom stupcu kao što je prikazano u nastavku.

To je sve.