Kuinka lisätä johtavia nollia Exceliin

Tämä opetusohjelma kattaa erilaiset menetelmät etunollien lisäämiseen tai pitämiseen sekä etunollien poistamiseen Excelissä.

Aina kun syötät tai tuot numeroita, joissa on yksi tai useampi etunolla, kuten 000652, Excel poistaa nämä nollat ​​automaattisesti, ja vain itse luku näkyy soluissa (652). Tämä johtuu siitä, että etunollat ​​eivät ole välttämättömiä laskelmissa eivätkä niitä lasketa.

Joskus kuitenkin tarvitaan näitä etunollia, kuten syöttäessäsi tunnusnumeroita, puhelinnumeroita, luottokorttinumeroita, tuotekoodeja tai postinumeroita jne. Onneksi Excel tarjoaa meille useita tapoja lisätä tai pitää etunollia soluissa. Tässä artikkelissa näytämme sinulle erilaisia ​​tapoja lisätä tai pitää etunollia ja poistaa etunollia.

Johtavien nollien lisääminen Exceliin

Pohjimmiltaan on kaksi tapaa, joilla voit lisätä etunollia: yksi, muotoile numerosi tekstiksi; kaksi, käytä mukautettua muotoilua lisätäksesi etunollia. Menetelmä, jota haluat käyttää, voi riippua siitä, mitä haluat tehdä numerolla.

Voit halutessasi lisätä etunollan, kun syötät yksilöllisiä henkilötunnuksia, tilinumeroita, sosiaaliturvatunnuksia tai postinumeroita jne. Et kuitenkaan aio käyttää näitä numeroita laskelmiin tai funktioissa, joten on parasta muuntaa ne. numerot tekstiin. Et koskaan laske tai keskittäisi puhelinnumeroita tai tilinumeroita.

Voit lisätä tai lisätä nollia numeroiden eteen useilla tavoilla muotoilemalla ne tekstiksi:

  • Solun muodon muuttaminen tekstiksi
  • Lisää heittomerkki (')
  • TEKSTI-toiminnon käyttö
  • REPT/LEN-toiminnon käyttö
  • Käytä CONCATENATE-funktiota/et-operaattoria (&)
  • OIKEA-toiminnon käyttö

Solumuodon muuttaminen tekstiksi

Tämä on yksi yksinkertaisimmista tavoista lisätä numeroihisi etunollia. Jos aiot vain syöttää numeroita ja haluat pitää etunollia kirjoittaessasi, tämä menetelmä on sinulle sopiva. Muuttamalla solumuodon yleisestä tai numerosta tekstiksi, voit pakottaa Excelin käsittelemään lukujasi tekstiarvoina ja kaikki soluun kirjoittamasi pysyvät täsmälleen ennallaan. Näin teet sen:

Valitse solu(t), johon haluat lisätä etunollia. Siirry Koti-välilehdelle, napsauta avattavaa Muoto-valikkoa Numerot-ryhmässä ja valitse muotovaihtoehdoista Teksti.

Nyt kun kirjoitat numeroita, Excel ei poista siitä mitään etunollaa.

Saatat nähdä pienen vihreän kolmion (virheilmaisin) solun vasemmassa yläkulmassa, ja kun valitset solun, se näyttää varoituskyltin, joka osoittaa, että olet tallentanut numeron tekstinä.

Poistaaksesi virheilmoituksen, valitse solu(t), napsauta varoitusmerkkiä ja valitse sitten luettelosta "Ohita virhe".

Voit myös kirjoittaa puhelinnumeroita välilyönnillä tai välilyönnillä numeroiden väliin, Excel käsittelee nämä numerot automaattisesti tekstinä.

Leadingin käyttäminen Apostrofi (')

Toinen tapa lisätä etunollia Excelissä on lisätä heittomerkki (') luvun alkuun. Tämä pakottaa Excelin syöttämään numeron tekstinä.

Kirjoita vain heittomerkki ennen numeroita ja paina Enter. Excel jättää alkunollat ​​ennalleen, mutta (') ei näy laskentataulukossa, ellet valitse solua.

Tekstifunktion käyttäminen

Yllä oleva menetelmä lisää nollia lukuihin kirjoittaessasi niitä, mutta jos sinulla on jo numeroluettelo ja haluat lisätä niiden eteen nollia, TEKSTI-toiminto on oikea tapa sinulle. TEKSTI-toiminnon avulla voit muuntaa numerot tekstijonoiksi samalla kun käytät mukautettua muotoilua.

TEKSTIN syntaksi-funktio:

= TEKSTI( arvo, muoto_teksti)

Missä,

  • arvo - Se on numeerinen arvo, joka sinun on muutettava tekstiksi ja käytettävä muotoilua.
  • muoto_teksti – on muoto, jota haluat käyttää.

TEKSTI-toiminnolla voit määrittää, kuinka monta numeroa numerossasi tulee olla. Jos esimerkiksi haluat numeroiden olevan 8 numeroa pitkiä, kirjoita 8 nollaa funktion toiseen argumenttiin: "00000000". Jos sinulla on 6-numeroinen luku solussa, toiminto lisää manuaalisesti 2 etunollaa ja jos sinulla on kaksinumeroisia lukuja, kuten 56, loput ovat nollia (00000056).

Jos haluat esimerkiksi lisätä etunollia ja tehdä numeroista 6-numeroisia, käytä tätä kaavaa:

=TEKSTI(A2"000000")

Koska meillä on 6 nollaa kaavan toisessa argumentissa, funktio muuntaa numeromerkkijonon tekstimerkkijonoksi ja lisää 5 etunollaa, jotta merkkijonosta tulee 6 numeroa pitkä.

merkintä: Muista laittaa muotokoodit funktioon lainausmerkkeihin.

Nyt voit käyttää samaa kaavaa muihin soluihin vetämällä täyttökahvaa. Kuten näet, toiminto muuntaa numerot teksteiksi ja lisää numeroihin alkunollat ​​siten, että numeroiden kokonaismäärä on 6.

TEKSTI-funktio palauttaa arvon aina tekstimerkkijonona, ei numerona, joten et voi käyttää niitä aritmeettisissa laskelmissa, mutta voit silti käyttää niitä hakukaavoissa, kuten VLOOKUP tai INDEX/MATCH hakeaksesi tiedot tuote käyttämällä tuotetunnuksia.

CONCATENATE-funktion/Et-operaattorin (&) käyttäminen

Jos haluat lisätä kiinteän määrän etunollia sarakkeen kaikkien numeroiden eteen, voit käyttää CONCATENATE-toimintoa tai et-operaattoria (&).

CONCATENATE-funktion syntaksi:

=CONCATENATE(teksti1, [teksti2], ...)

Missä,

teksti1 - Numeron eteen lisättävien nollien lukumäärä.

teksti 2 - Alkuperäinen numero tai soluviittaus

Et-operaattorin syntaksi:

=Arvo_1 & Arvo_2

Missä,

Arvo_1 on etunollat, jotka lisätään ennen numeroa, ja Arvo_2 on numero.

Jos esimerkiksi haluat lisätä vain kaksi nollaa ennen numeroa, käytä jompaakumpaa seuraavista kaavista:

=CONCATENATE("00",A2)

Ensimmäinen argumentti on kaksi nollaa ("00"), koska haluamme täyttää kaksi nollaa ennen A2:n numeroa (joka on toinen argumentti).

Tai, 

="00"&A2

Tässä ensimmäinen argumentti on 2 nollaa, jota seuraa '&'-operaattori, ja toinen argumentti on numero.

Kuten näet, kaava lisää vain kaksi etunollaa kaikkiin sarakkeen numeroihin riippumatta siitä, kuinka monta numeroa numero sisältää.

Molemmat näistä kaavoista yhdistävät tietyn määrän nollia ennen alkuperäisiä numeroita ja tallentavat ne tekstijonoina.

REPT/LEN-toiminnon käyttö

Jos haluat lisätä etunollia numeeriseen tai aakkosnumeeriseen dataan ja muuntaa merkkijonon tekstiksi, käytä REPT-toimintoa. REPT-toimintoa käytetään toistamaan merkki(t) tietyn määrän kertoja. Tätä toimintoa voidaan käyttää myös kiinteiden etunollien lisäämiseen ennen numeroa.

=REPT(teksti, numero_ kertaa)

Missä "teksti" on merkki, jonka haluamme toistaa (tapauksessamme "0") ja "number_times" -argumentti on kuinka monta kertaa haluamme toistaa kyseisen merkin.

Jos esimerkiksi luodaan viisi nollaa ennen numeroita, kaava näyttää tältä:

=TOISTA(0,5)&A2

Kaava toistaa 5 nollaa ja yhdistää A2:n lukujonon ja palauttaa tuloksen. Sitten kaavaa sovelletaan soluun B2:B6 käyttämällä täyttökahvaa.

Yllä oleva kaava lisää kiinteän määrän nollia numeron eteen, mutta luvun kokonaispituus vaihtelee luvun mukaan.

Jos haluat lisätä etunollia minne tahansa tietyn merkin pituisten (kiinteäpituisten) merkkijonojen luomiseksi, voit käyttää REPT- ja LEN-funktioita yhdessä.

Syntaksi:

=REPT(teksti, numero_ kertaa-LEN(teksti))&solu

Jos haluat esimerkiksi lisätä etuliitteen nollia A2:n arvoon ja luoda 5 merkin pituisen merkkijonon, kokeile tätä kaavaa:

=TOISTA(0,5-PITO(A2))&A2

Tässä 'PITO(A2)' saa solun A2 merkkijonon/numeroiden kokonaispituuden. "5" on merkkijonon/numeroiden enimmäispituus, jonka solussa tulee olla. Ja 'REPT(0,5-LEN(A2))'-osa lisää nollien määrän vähentämällä A2:n merkkijonon pituuden nollien enimmäismäärästä (5). Sitten joukko nollia yhdistetään ennen A2:n arvoa kiinteäpituiseksi merkkijonoksi.

OIKEA-toiminnon käyttäminen

Toinen tapa lisätä etunollia ennen merkkijonoa Excelissä on käyttää OIKEA-funktiota.

OIKEA-funktio voi lisätä useita nollia luvun alkuun ja poimia arvosta oikeanpuoleisimman N merkkiä.

Syntaksi:

= OIKEA (teksti, merkkien määrä)
  • teksti on solu tai arvo, josta haluat poimia merkkejä.
  • merkkien_määrä on tekstistä poimittavien merkkien määrä. Jos tätä argumenttia ei anneta, vain ensimmäinen merkki puretaan.

Tässä menetelmässä ketjutamme enimmäismäärän nollia soluviittauksella, joka sisältää merkkijonon "teksti"-argumentissa.

Jos haluat luoda 6-numeroisen luvun, joka perustuu A:n numeromerkkijonoon, jonka alussa on nollia, kokeile tätä kaavaa:

=OIKEA("0000000"&A2,6)

Kaavan ensimmäinen argumentti (teksti) lisää 7 ​​nollaa A2:n arvoon ("0000000"&A2) ja palauttaa sitten oikeanpuoleiset 7 merkkiä, mikä johtaa joihinkin alkunolliin.

Etunollien lisääminen mukautetulla numeromuotoilulla

Jos käytät jotakin yllä olevista tavoista asettaaksesi alkunollat ​​ennen numeroita, saat aina tekstimerkkijonon, ei numeron. Eikä niistä ole paljon hyötyä laskelmissa tai numeerisissa kaavoissa.

Paras tapa lisätä etunollia Excelissä on käyttää mukautettua numeromuotoilua. Jos lisäät etunollia lisäämällä mukautetun numeromuodon soluun, se ei muuta solun arvoa, vaan vain tapaa, jolla se näytetään. Arvo säilyy silti numerona, ei tekstinä.

Voit muuttaa solujen numeromuotoilua seuraavasti:

Valitse solu tai solualue, jossa haluat näyttää etunollat. Napsauta sitten hiiren kakkospainikkeella mitä tahansa valitulla alueella ja valitse kontekstivalikosta Muotoile solut. Tai paina pikanäppäimiä Ctrl + 1.

Siirry Muotoile solut -ikkunassa Numero-välilehteen ja valitse luokkavaihtoehdoista Mukautettu.

Kirjoita nollien määrä "Type:" -ruutuun määrittääksesi solussa näytettävän numeron kokonaismäärän. Jos esimerkiksi haluat numeron olevan 6 numeroa pitkä, kirjoita mukautetun muotokoodiksi "000000". Ota sitten käyttöön napsauttamalla OK.

Tämä näyttää etunollat ​​ennen numeroita ja jos numerossa on alle 6 numeroa, se täyttää nollan ennen sitä.

Numeroissa näyttää olevan vain etunollia, kun taas taustalla oleva arvo pysyy muuttumattomana. Jos valitset mukautetun muotoilun solun, se näyttää alkuperäisen numeron kaavapalkissa

Voit käyttää mukautetussa numeromuodossasi monia digitaalisia paikkamerkkejä. Mutta on vain kaksi ensisijaista paikkamerkkiä, joita voit käyttää numeroiden alkunollien lisäämiseen.

  • 0 – Se on numeropaikkamerkki, joka näyttää ylimääräisiä nollia. Se näyttää pakotetut numerot 0-9 riippumatta siitä, liittyykö numero arvoon vai ei. Jos esimerkiksi kirjoitat 2.5 muotokoodilla 000.00, se näyttää 002.50.
  • # – Se on numeropaikkamerkki, joka näyttää valinnaiset numerot eikä sisällä ylimääräisiä nollia. Jos esimerkiksi kirjoitat 123 muotoilukoodilla 000#, se näyttää 0123.

Myös kaikki muotokoodiin sisällyttämäsi välimerkit tai muut merkit näytetään sellaisenaan. Voit käyttää merkkejä, kuten yhdysmerkkiä (-), pilkkua (,), kauttaviivaa (/) jne.

Voit esimerkiksi muotoilla numerot puhelinnumeroiksi käyttämällä mukautettua muotoa.

Muotoile koodia Muotoile solut -valintaikkuna:

Lopputulos:

Käytetään tätä muotoilukoodia seuraavassa esimerkissä:

##0000

Kuten näette, "0" lisää ylimääräisiä nollia, kun taas "#" ei lisää merkityksettömiä nollia:

Voit myös käyttää ennalta määritettyjä muotokoodeja Muotoile soluja -valintaikkunan Erikoismuodot-osiossa postinumeroille, puhelinnumeroille ja sosiaaliturvatunnuksille.

Seuraavassa taulukossa näkyvät numerot, joiden alussa on nollia, joissa eri "erikois"muotoisia koodeja käytetään eri sarakkeissa:

Johtavien nollien poistaminen Excelissä

Nyt olet oppinut lisäämään etunollia Excelissä, katsotaanpa kuinka poistaa etunollat ​​merkkijonojen lukumäärästä. Joskus, kun tuot tietoja ulkoisesta lähteestä, numeroiden etuliitteet voivat olla nollia ja ne voidaan muotoilla tekstiksi. Tällaisissa tapauksissa sinun on poistettava etunollat ​​ja muutettava ne takaisin numeroiksi, jotta voit käyttää niitä kaavoissa.

On useita tapoja, joilla voit poistaa etunollat ​​Excelissä, ja näemme ne yksitellen.

Poista johtavat nollat ​​muuttamalla solujen muotoilua

Jos etunollat ​​lisättiin mukautetulla numeromuotoilulla, voit helposti poistaa ne muuttamalla solujen muotoa. Voit selvittää, onko solusi mukautettu muotoiltu, katsomalla osoitepalkkia (nollat ​​näkyvät solussa, eivät osoitepalkissa).

Voit poistaa etuliitteen nollat ​​valitsemalla solut, joiden alussa on nollia, napsauttamalla Numeromuoto-ruutua ja valitsemalla "Yleinen" tai "Numero" -muotoiluvaihtoehto.

Nyt etunollat ​​ovat poissa:

Poista etunollat ​​muuntamalla teksti numeroiksi

Jos etunollasi on lisätty muuttamalla solumuotoa tai lisäämällä heittomerkkejä numeroiden eteen tai lisätty automaattisesti dataa tuottaessa, helpoin tapa muuntaa ne numeroiksi on käyttää Virheentarkistus-vaihtoehtoa. Näin teet sen:

Voit käyttää tätä menetelmää, jos numerot on tasattu vasemmalle ja soluissasi on pieni vihreä kolmio (virheilmaisin) solujen vasemmassa yläkulmassa. Tämä tarkoittaa, että numerot on muotoiltu tekstiksi.

Valitse nämä solut ja näet keltaisen varoituksen valinnan oikeassa yläkulmassa. Napsauta sitten "Muunna numeroksi" -vaihtoehtoa avattavasta valikosta.

Nollasi poistetaan ja luvut muunnetaan takaisin numeromuotoon (oikealle tasattuna).

Johtavien nollien poistaminen Kerro/jako 1:llä

Toinen helppo ja paras tapa poistaa etunus on kertoa tai jakaa luvut 1:llä. Arvon jakaminen tai kertominen ei muuta arvoa, se yksinkertaisesti muuntaa arvon takaisin luvuksi ja poistaa alkunollat.

Voit tehdä tämän kirjoittamalla alla olevan esimerkin kaavan soluun ja painamalla ENTER. Alkunollat ​​poistetaan ja merkkijono muunnetaan takaisin luvuksi.

Käytä sitten tätä kaavaa muihin soluihin täyttökahvan avulla.

Voit saavuttaa samat tulokset käyttämällä "Paste Special" -komentoa. Näin:

Kirjoita soluun numeroarvo "1" (esimerkiksi B2:ssa) ja kopioi se.

Valitse seuraavaksi solut, joista haluat poistaa etunollat. Napsauta sitten valintaa hiiren kakkospainikkeella ja valitse sitten Liitä määräten -vaihtoehto.

Valitse Liitä määräten -valintaikkunan Toiminto-kohdassa joko "Kerto" tai "Jaa" ja napsauta "OK".

Siinä kaikki, etunollasi poistetaan ja merkkijonot jäävät numeroiksi.

Poista johtavat nollat ​​käyttämällä kaavoja

Toinen helppo tapa poistaa etuliitteitä nollia on käyttää ARVO-funktiota. Tämä menetelmä voi olla hyödyllinen riippumatta siitä, onko etunollasi lisätty käyttämällä toista kaavaa tai heittomerkkiä tai mukautettua muotoilua.

=ARVO(A1)

Kaavan argumentti voi olla arvo tai soluviittaus, jolla on arvo. Kaava poistaa etunollat ​​ja muuntaa arvon tekstistä numeroksi. Käytä sitten kaavaa muihin soluihin.

Joskus saatat haluta poistaa etunollat, mutta haluat säilyttää numerot tekstimuodossa. Tällaisissa tapauksissa sinun on käytettävä TEXT()- ja VALUE ()-funktioita yhdessä seuraavasti:

=TEKSTI(ARVO(A1),"#")

ARVO-funktio muuntaa A1:ssä olevan arvon numeroksi. Mutta toinen argumentti "#" muuntaa arvon takaisin tekstimuotoon ilman ylimääräisiä nollia. Tämän seurauksena saat numerot ilman etunollia, mutta silti tekstimuodossa (vasemmalle tasattuna).

Poista johtavat nollat ​​Excelin tekstistä sarakkeiksi -ominaisuuden avulla

Vielä yksi tapa poistaa etunollat ​​on käyttää Excelin Text to Columns -ominaisuutta.

Valitse solualue, jossa on numerot, joiden alussa on nollia.

Siirry seuraavaksi "Data"-välilehteen ja napsauta "Teksti sarakkeisiin" -painiketta Tietotyökalut-ryhmässä.

"Muunna teksti sarakkeiksi" -velho tulee näkyviin. Vaiheessa 1/3 valitse "Rajattu" ja napsauta "Seuraava".

Vaiheessa 2/3 poista kaikkien erottimien valinnat ja napsauta "Seuraava".

Jätä viimeisessä vaiheessa saraketietojen muoto -asetukseksi "Yleinen" ja valitse kohde (alueen ensimmäinen solu), johon haluat numerot ilman etunollia. Napsauta sitten "Valmis"

Ja saat numerot, joissa on alkupäät poistettu, erilliseen sarakkeeseen alla olevan kuvan mukaisesti.

Siinä kaikki.