Kako dodati vodilne ničle v Excelu

Ta vadnica zajema različne metode dodajanja ali ohranjanja vodilnih ničel ter odstranjevanja vodilnih ničel v Excelu.

Kadar koli vnesete ali uvozite številke z eno ali več začetnimi ničlami, na primer 000652, Excel samodejno odstrani te ničle in v celicah (652) se prikaže samo število. To je zato, ker vodilne ničle niso potrebne za izračune in se ne štejejo.

Vendar pa obstajajo časi, ko so te vodilne ničle potrebne, na primer pri vnašanju identifikacijskih številk, telefonskih številk, številk kreditnih kartic, kod izdelkov ali poštnih številk itd. Na srečo nam Excel ponuja več načinov za dodajanje ali ohranjanje začetnih ničel v celicah. V tem članku vam bomo pokazali različne načine dodajanja ali ohranjanja vodilnih ničel in odstranjevanja vodilnih ničel.

Dodajanje vodilnih ničel v Excelu

V bistvu obstajata dve metodi, ki ju lahko uporabite za dodajanje vodilnih ničel: ena, formatirajte svojo številko kot 'Besedilo'; drugič, uporabite oblikovanje po meri za dodajanje vodilnih ničel. Metoda, ki jo želite uporabiti, je lahko odvisna od tega, kaj želite narediti s številko.

Morda boste želeli dodati vodilno ničlo, ko vnašate edinstvene ID številke, številke računov, številke socialnega zavarovanja ali poštne številke itd. Vendar teh številk ne boste uporabljali za izračune ali funkcije, zato je najbolje, da jih pretvorite številke v besedilo. Nikoli ne bi sešteli ali povprečili telefonskih številk ali številk računov.

Obstaja več načinov za dodajanje ali vstavljanje ničle pred številkami, tako da jih oblikujete kot besedilo:

  • Spreminjanje oblike celice v Besedilo
  • Dodajanje apostrofa (‘)
  • Uporaba funkcije TEXT
  • Uporaba funkcije REPT/LEN
  • Uporabi funkcijo CONCATENATE/operater ampersand (&)
  • Uporaba funkcije DESNO

Spreminjanje oblike celice v besedilo

To je eden najpreprostejših načinov za dodajanje vodilnih ničel vašim številkam. Če boste samo vnesli številke in želite, da med tipkanjem ostanejo vodilne ničle, je to pravi način za vas. Če spremenite obliko celice iz splošne ali številke v besedilo, lahko prisilite Excel, da vaše številke obravnava kot besedilne vrednosti in vse, kar vnesete v celico, bo ostalo popolnoma enako. Evo, kako to storite:

Izberite celice, v katere želite dodati vodilne ničle. Pojdite na zavihek »Domov«, kliknite spustno polje »Oblika« v skupini Številke in med možnostmi oblike izberite »Besedilo«.

Zdaj, ko vnesete svoje številke, Excel iz nje ne bo izbrisal nobene vodilne ničle.

V zgornjem levem kotu celice boste morda videli majhen zeleni trikotnik (indikator napake) in ko izberete to celico, vam bo prikazal opozorilni znak, ki označuje, da ste številko shranili kot besedilo.

Če želite odstraniti sporočilo o napaki, izberite celico(e), kliknite na opozorilni znak in nato s seznama izberite »Prezri napako«.

Telefonske številke lahko vnesete tudi s presledkom ali vezajem med številkami, Excel bo te številke samodejno obravnaval kot besedilo.

Uporaba Leading Apostrof (')

Drug način za dodajanje vodilnih ničel v Excelu je, da na začetek števila dodate apostrof (‘). To bo prisililo Excel, da vnese številko kot besedilo.

Samo vnesite apostrof pred katero koli številko in pritisnite "Enter". Excel bo pustil vodilne ničle nedotaknjene, vendar (‘) ne bo viden na delovnem listu, razen če izberete celico.

Uporaba funkcije besedila

Zgornja metoda dodaja ničle številkam, ko jih vnašate, če pa že imate seznam številk in želite pred njimi vstaviti vodilne ničle, potem je funkcija BESEDILO prava metoda za vas. Funkcija TEXT vam omogoča pretvorbo številk v besedilne nize ob uporabi oblikovanja po meri.

Sintaksa funkcije TEXT:

= BESEDILO (vrednost, format_besedilo)

Kje,

  • vrednost - To je številčna vrednost, ki jo morate pretvoriti v besedilo in uporabiti oblikovanje.
  • format_besedilo – je oblika, ki jo želite uporabiti.

S funkcijo BESEDILO lahko določite, koliko števk naj ima vaša številka. Na primer, če želite, da so vaše številke dolge 8 števk, vnesite 8 ničel v drugi argument funkcije: "00000000". Če imate v celici 6-mestno številko, bo funkcija ročno dodala 2 vodilni ničli in če imate 2-mestno številko, kot je 56, bodo ostale ničle (00000056).

Če želite na primer dodati vodilne ničle in narediti številke 6-mestne, uporabite to formulo:

=BESEDILO(A2,"000000")

Ker imamo v drugem argumentu formule 6 ničel, funkcija pretvori številski niz v besedilni niz in doda 5 vodilnih ničel, da je niz dolg 6 števk.

Opomba: Prosimo, ne pozabite priložiti kode formata v dvojne narekovaje v funkciji.

Zdaj lahko uporabite isto formulo za preostale celice tako, da povlečete ročico za polnjenje. Kot lahko vidite, funkcija pretvori številke v besedila in številkam doda vodilne ničle, tako da je skupno število števk 6.

Funkcija TEXT bo vedno vrnila vrednost kot besedilni niz, ne kot število, zato jih ne boste mogli uporabiti v aritmetičnih izračunih, vendar jih lahko še vedno uporabite v formulah za iskanje, kot sta VLOOKUP ali INDEX/MATCH, da pridobite podrobnosti o izdelek z ID-ji izdelkov.

Uporaba funkcije CONCATENATE/operater ampersand (&)

Če želite pred vsemi številkami v stolpcu dodati fiksno število začetnih ničel, lahko uporabite funkcijo CONCATENATE ali operator ampersand (&).

Sintaksa funkcije CONCATENATE:

=VEZI (besedilo1, [besedilo2], ...)

Kje,

besedilo 1 - Število ničel, ki jih je treba vstaviti pred številko.

besedilo 2 - Izvirna številka ali sklic na celico

Sintaksa operatorja Ampersand:

=Vrednost_1 & Vrednost_2

Kje,

Vrednost_1 je vodilne ničle, ki jih je treba vstaviti pred številko, Vrednost_2 pa je število.

Na primer, če želite dodati samo dve ničli pred številko, uporabite eno od te formule:

=VEŽI ("00",A2)

Prvi argument sta dve ničli (»00«), ker želimo vstaviti dve ničli pred številko v A2 (ki je drugi argument).

ali, 

="00"&A2

Tukaj sta prvi argument 2 ničli, ki jima sledi operator '&', drugi argument pa je število.

Kot lahko vidite, formula doda samo dve začetni ničli vsem številkam v stolpcu, ne glede na to, koliko števk število vsebuje.

Obe formuli združita določeno število ničel pred prvotnimi številkami in jih shrani kot besedilne nize.

Uporaba funkcije REPT/LEN

Če želite številskim ali alfanumeričnim podatkom dodati vodilne ničle in pretvoriti niz v besedilo, uporabite funkcijo REPT. Funkcija REPT se uporablja za ponavljanje znakov(-ov) določeno število krat. To funkcijo lahko uporabite tudi za vstavljanje fiksnih številk začetnih ničel pred številko.

=REPT(besedilo, število_krat)

Kjer je "besedilo" znak, ki ga želimo ponoviti (v našem primeru "0"), argument "število_krat" pa je število, ko želimo ta znak ponoviti.

Na primer, če želite ustvariti pet ničel pred številkami, bi bila formula videti tako:

=REPT(0,5)&A2

Formula ponovi 5 ničel in združi številski niz v A2 ter vrne rezultat. Nato se formula uporabi za celico B2:B6 z uporabo ročice za polnjenje.

Zgornja formula dodaja fiksno število ničel pred številom, vendar se skupna dolžina števila razlikuje glede na število.

Če želite dodati vodilne ničle povsod, kjer je to potrebno za ustvarjanje nizov z določeno dolžino znakov (fiksne dolžine), lahko uporabite funkcije REPT in LEN skupaj.

sintaksa:

=REPT(besedilo, število_krat-LEN(besedilo))&celica

Če želite na primer vrednosti v A2 dodati ničle s predpono in ustvariti 5-mestni niz, poskusite s to formulo:

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

Tukaj 'LEN(A2)' dobi skupno dolžino niza/številk v celici A2. '5' je največja dolžina niza/številk, ki jih mora imeti celica. In del »REPT(0,5-LEN(A2))« doda število ničel tako, da od največjega števila nič (5) odšteje dolžino niza v A2. Nato se pred vrednostjo A2 združi število 0, da se naredi niz s fiksno dolžino.

Uporaba funkcije DESNO

Drug način za vstavljanje vodilnih ničel pred nizom v Excelu je uporaba funkcije DESNO.

Funkcija DESNO lahko doda število ničel na začetek števila in iz vrednosti izvleče skrajnih N znakov.

sintaksa:

= DESNO (besedilo, število_znakov)
  • besedilo je celica ali vrednost, iz katere želite izvleči znake.
  • število_znakov je število znakov, ki jih je treba izvleči iz besedila. Če ta argument ni podan, bo izvlečen samo prvi znak.

Za to metodo povežemo največje število ničel s sklicevanjem na celico, ki vsebuje niz v argumentu »besedilo«.

Če želite ustvariti 6-mestno število na podlagi številskega niza v A z vodilnimi ničlami, poskusite s to formulo:

=DESNO("0000000"&A2,6)

Prvi argument (besedilo) formule doda 7 ničel k vrednosti v A2 (»0000000«&A2) in nato vrne skrajnih 7 znakov, kar ima za posledico nekaj vodilnih ničel.

Dodajanje vodilnih nič z uporabo oblikovanja številk po meri

Če uporabite katero od zgornjih metod, da pred številkami postavite vodilne ničle, boste vedno dobili besedilni niz in ne številke. In ne bodo veliko uporabni pri izračunih ali v številskih formulah.

Najboljši način za dodajanje vodilnih ničel v Excelu je uporaba oblikovanja števil po meri. Če dodate vodilne ničle tako, da celici dodate obliko številk po meri, to ne spremeni vrednosti celice, ampak samo način, kako je prikazana. Vrednost bo še vedno ostala kot številka, ne besedilo.

Če želite spremeniti oblikovanje številk celic, sledite tem korakom:

Izberite celico ali obseg celic, kjer želite prikazati vodilne ničle. Nato z desno tipko miške kliknite kjer koli v izbranem obsegu in v kontekstnem meniju izberite možnost »Oblikuj celice«. Ali pa pritisnite bližnjične tipke Ctrl + 1.

V oknu Oblikovanje celic pojdite na zavihek »Število« in v možnostih kategorije izberite »Po meri«.

V polje »Vrsta:« vnesite število ničel, da določite skupno število števk, ki jih želite prikazati v celici. Na primer, če želite, da je številka 6-mestna, vnesite »000000« kot kodo oblike po meri. Nato kliknite »V redu«, da uporabite.

To bo prikazalo vodilne ničle pred številkami in če je število manjše od 6 števk, se pred njo doda nič.

Zdi se, da imajo številke samo vodilne ničle, medtem ko bi osnovna vrednost ostala nespremenjena. Če izberete celico z oblikovanjem po meri, vam bo v vrstici s formulo prikazala izvirno številko

Obstaja veliko digitalnih ograd, ki jih lahko uporabite v svoji obliki številk po meri. Vendar obstajata samo dve primarni označbi mesta, ki ju lahko uporabite za dodajanje vodilnih ničel v številke.

  • 0 – To je oznaka mesta, ki prikazuje dodatne ničle. Prikaže vsiljene števke 0-9, ne glede na to, ali je številka pomembna za vrednost ali ne. Če na primer vnesete 2.5 s kodo formata 000.00, se prikaže 002.50.
  • # – To je nadomestni znak za števke, ki prikazuje neobvezne številke in ne vključuje dodatnih ničel. Če na primer vnesete 123 s kodo oblikovanja 000#, se prikaže 0123.

Prav tako bodo vsi ločili ali drugi znaki, ki jih vključite v kodo formata, prikazani takšni, kot so. Uporabite lahko znake, kot so vezaj (-), vejica (,), poševnica naprej (/) itd.

Številke lahko na primer oblikujete tudi kot telefonske številke z uporabo oblike po meri.

Oblika kode v pogovornem oknu Oblikovanje celic:

rezultat:

Uporabimo to oblikovno kodo v naslednjem primeru:

##0000

Kot lahko vidite, bo "0" dodal dodatne ničle, medtem ko "#" ne doda nepomembnih ničel:

Uporabite lahko tudi vnaprej določene kode formata v razdelku »Posebni formati« pogovornega okna Oblikovanje celic za poštne številke, telefonske številke in številke socialnega zavarovanja.

Naslednja tabela prikazuje številke z vodilnimi ničlami, pri katerih se za različne stolpce uporabljajo različne kode "posebnega" formata:

Odstranjevanje vodilnih ničel v Excelu

Zdaj ste se naučili, kako dodati vodilne ničle v Excelu, poglejmo, kako odstraniti vodilne ničle iz števila nizov. Včasih, ko uvozite podatke iz zunanjega vira, lahko številke na koncu imajo predpono nič in so oblikovane kot besedilo. V takih primerih morate odstraniti vodilne ničle in jih pretvoriti nazaj v številke, da jih lahko uporabite v formulah.

V Excelu lahko odstranite vodilne ničle na različne načine in videli jih bomo eno za drugo.

Odstranite vodilne ničle s spremembo oblikovanja celice

Če so bile vodilne ničle dodane z oblikovanjem števil po meri, jih lahko preprosto odstranite tako, da spremenite obliko celic. Če pogledate v naslovno vrstico, lahko ugotovite, ali so vaše celice oblikovane po meri (ničele bodo vidne v celici ne v naslovni vrstici).

Če želite odstraniti ničle s predpono, izberite celice z vodilnimi ničlami, kliknite polje »Oblika številk« in izberite možnost oblikovanja »Splošno« ali »Številčno«.

Zdaj so vodilne ničle izginile:

Izbrišite vodilne ničle s pretvorbo besedila v številke

Če so bile vaše vodilne ničle dodane s spremembo formata celice ali z dodajanjem apostrofov pred številkami ali samodejno dodanimi pri uvažanju podatkov, je najlažji način, da jih pretvorite v številke, z možnostjo preverjanja napak. Evo, kako to storite:

To metodo lahko uporabite, če so vaše številke levo poravnane in imajo vaše celice majhen zeleni trikotnik (indikator napake) v zgornjem levem kotu celic. To pomeni, da so številke oblikovane kot besedilo.

Izberite te celice in v zgornjem desnem delu izbire boste videli rumeno opozorilo. Nato v spustnem meniju kliknite možnost »Pretvori v številko«.

Vaše ničle bodo odstranjene in številke bodo pretvorjene nazaj v obliko številk (poravnano desno).

Odstranjevanje vodilnih ničel z Pomnožite/delite z 1

Drug preprost in najboljši način za odstranitev vodilnih številk je množenje ali deljenje števil z 1. Deljenje ali množenje vrednosti ne spremeni vrednosti, ampak preprosto pretvori vrednost nazaj v številko in odstrani vodilne ničle.

Če želite to narediti, vnesite formulo v spodnjem primeru v celico in pritisnite ENTER. Vodilne ničle bodo odstranjene in niz bo pretvorjen nazaj v številko.

Nato uporabite to formulo za druge celice z uporabo ročice za polnjenje.

Enake rezultate lahko dosežete z ukazom »Prilepi posebno«. Takole:

Vnesite številčno vrednost '1' v celico (recimo v B2) in kopirajte to vrednost.

Nato izberite celice, v katerih želite odstraniti vodilne ničle. Nato z desno tipko miške kliknite izbor in nato izberite možnost »Posebno lepljenje«.

V pogovornem oknu Posebno lepljenje v razdelku Operacija izberite možnost »Množi« ali »Deli« in kliknite »V redu«.

To je to, vaše vodilne ničle bodo odstranjene, nizi pa bodo ostali kot številke.

Odstranite vodilne ničle z uporabo formule

Drug preprost način za brisanje ničel s predpono je uporaba funkcije VALUE. Ta metoda bi lahko bila uporabna, ne glede na to, ali so bile vaše vodilne ničle dodane z uporabo druge formule ali apostrofa ali z oblikovanjem po meri.

=VREDNOST(A1)

Argument formule je lahko vrednost ali sklic na celico, ki ima vrednost. Formula odstrani vodilne ničle in pretvori vrednost iz besedila v številko. Nato nanesite formulo na preostale celice.

Včasih boste morda želeli odstraniti vodilne ničle, vendar želite številke obdržati v besedilni obliki. V takih primerih morate funkcije TEXT() in VALUE () uporabiti skupaj, kot je ta:

=BESEDILO(VREDNOST(A1),"#")

Funkcija VALUE pretvori vrednost v A1 v število. Toda drugi argument, '#', pretvori vrednost nazaj v obliko besedila brez dodatnih ničel. Posledično bi dobili številke brez začetnih ničel, vendar še vedno v besedilni obliki (levo poravnano).

Odstranite vodilne ničle z uporabo Excelove funkcije Besedilo v stolpce

Še en način za odstranitev vodilnih ničel je uporaba Excelove funkcije Besedilo v stolpce.

Izberite obseg celic, ki imajo številke z vodilnimi ničlami.

Nato pojdite na zavihek »Podatki« in kliknite gumb »Besedilo v stolpce« v skupini Podatkovna orodja.

Prikaže se čarovnik »Pretvori besedilo v stolpce«. V koraku 1 od 3 izberite »Delimited« in kliknite »Naprej«.

V koraku 2 od 3 počistite vsa ločila in kliknite »Naprej«.

V zadnjem koraku pustite možnost za obliko podatkov stolpca kot »Splošno« in izberite cilj (prva celica obsega), kjer želite svoje številke brez vodilnih ničel. Nato kliknite »Dokončaj«

Številke z odstranjenimi vodilnimi številkami boste dobili v ločenem stolpcu, kot je prikazano spodaj.

To je vse.