Cum să adăugați zerouri de început în Excel

Acest tutorial acoperă diferitele metode de adăugare sau păstrare a zerourilor inițiale, precum și de eliminare a zerourilor de început în Excel.

Ori de câte ori introduceți sau importați numere cu unul sau mai multe zerouri inițiale, cum ar fi 000652, Excel elimină automat acele zerouri și numai numărul în sine apare în celule (652). Acest lucru se datorează faptului că zerourile de început nu sunt necesare pentru calcule și nu contează.

Cu toate acestea, există momente când acele zerouri înainte sunt necesare, cum ar fi atunci când introduceți numere de identificare, numere de telefon, numere de card de credit, coduri de produse sau coduri poștale etc. Din fericire, Excel ne oferă mai multe modalități de a adăuga sau de a păstra zerouri de început. în celule. În acest articol, vă vom arăta diferitele modalități de a adăuga sau de a păstra zerourile de început și de a elimina zerourile de început.

Adăugarea de zerouri principale în Excel

În esență, există 2 metode pe care le puteți folosi pentru a adăuga zerouri de început: unul, formatați numărul ca „Text”; doi, utilizați formatarea personalizată pentru a adăuga zerouri de început. Metoda pe care doriți să o utilizați poate depinde de ceea ce doriți să faceți cu numărul.

Poate doriți să adăugați zero înainte atunci când introduceți numere de identificare unice, numere de cont, numere de securitate socială sau coduri poștale etc. Dar, nu veți folosi aceste numere pentru calcule sau în funcții, așa că cel mai bine este să le convertiți. numere la text. Nu ați însumat sau media numerele de telefon sau numerele de cont niciodată.

Există mai multe moduri în care puteți adăuga sau introduce zerouri înaintea numerelor, formatându-le ca text:

• Schimbarea formatului celulei în Text
• Adăugarea unui apostrof (‘)
• Folosind funcția TEXT
• Utilizarea funcției REPT/LEN
• Utilizați funcția CONCATENATE/operatorul Ampersand (&)
• Folosind funcția RIGHT

Schimbarea formatului celulei în text

Aceasta este una dintre cele mai simple moduri de a adăuga zerouri la numerele tale. Dacă doar introduceți numere și doriți să păstrați zerourile inițiale pe măsură ce introduceți, atunci aceasta este metoda pentru dvs. Schimbând formatul celulei din General sau Număr în Text, puteți forța Excel să vă trateze numerele ca valori de text și orice introduceți în celulă va rămâne exact la fel. Iată cum o faci:

Selectați celula(ele) în care doriți să adăugați zerouri de început. Accesați fila „Acasă”, faceți clic pe caseta drop-down „Format” din grupul Numere și selectați „Text” din opțiunile de format.

Acum, când introduceți numerele, Excel nu va șterge niciun zero de la început.

Este posibil să vedeți un mic triunghi verde (indicator de eroare) în colțul din stânga sus al celulei și când selectați acea celulă, acesta vă va afișa un semn de avertizare care indică faptul că ați stocat numărul ca text.

Pentru a elimina mesajul de eroare, selectați celula(ele), faceți clic pe semnul de avertizare și apoi selectați „Ignorați eroarea” din listă.

De asemenea, puteți introduce numere de telefon cu spațiu sau cratimă între numere, Excel va trata automat aceste numere ca text.

Folosind Leading Apostrof ( ‘ )

O altă modalitate de a adăuga zerouri de început în Excel este să adăugați un apostrof (‘) la începutul numărului. Acest lucru va forța Excel să introducă un număr ca text.

Introduceți un apostrof înaintea oricăror numere și apăsați „Enter”. Excel va lăsa zerourile inițiale intacte, dar (‘) nu va fi vizibil în foaia de lucru decât dacă selectați celula.

Utilizarea funcției de text

Metoda de mai sus adaugă zerouri numerelor pe măsură ce le introduceți, dar dacă aveți deja o listă de numere și doriți să introduceți zerouri înaintea lor, atunci funcția TEXT este metoda potrivită pentru dvs. Funcția TEXT vă permite să convertiți numere în șiruri de text în timp ce aplicați formatare personalizată.

Sintaxa funcției TEXT:

= TEXT(valoare, format_text)

Unde,

• valoare - Este valoarea numerică pe care trebuie să o convertiți în text și să aplicați formatarea.
• format_text – este formatul pe care doriți să îl aplicați.

Cu funcția TEXT, puteți specifica câte cifre ar trebui să aibă numărul dvs. De exemplu, dacă doriți ca numerele dvs. să aibă 8 cifre, atunci introduceți 8 zerouri în al doilea argument al funcției: „00000000”. Dacă aveți un număr de 6 cifre într-o celulă, atunci funcția va adăuga manual 2 zerouri de început și dacă aveți numere de 2 cifre, cum ar fi 56, restul vor fi zerouri (00000056).

De exemplu, pentru a adăuga zerouri inițiale și a face ca numerele să aibă 6 cifre, utilizați această formulă:

=TEXT(A2,"000000")

Deoarece avem 6 zerouri în al doilea argument al formulei, funcția convertește șirul de numere într-un șir de text și adaugă 5 zerouri de început pentru a face șirul de 6 cifre lung.

Notă: Vă rugăm să nu uitați să includeți codurile de format între ghilimele duble în funcție.

Acum puteți aplica aceeași formulă pentru restul celulelor trăgând mânerul de umplere. După cum puteți vedea, funcția convertește numerele în texte și adaugă zerouri de început la numere, astfel încât numărul total de cifre să fie 6.

Funcția TEXT va returna întotdeauna valoare ca șir de text, nu ca număr, așa că nu le veți putea folosi în calcule aritmetice, dar le puteți utiliza în continuare în formule de căutare, cum ar fi CĂUTARE V sau INDEX/POTRIVIRE pentru a obține detaliile unui produs folosind ID-uri de produs.

Folosind funcția CONCATENATE/operatorul Ampersand (&)

Dacă doriți să adăugați un număr fix de zerouri înaintea tuturor numerelor dintr-o coloană, puteți utiliza funcția CONCATENATE sau operatorul ampersand (&).

Sintaxa funcției CONCATENATE:

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

Unde,

text1 - Numărul de zerouri care trebuie introdus înaintea numărului.

text2 - Numărul original sau referința la celulă

Sintaxa operatorului Ampersand:

=Valoarea_1 și Valoarea_2 

Unde,

Valoarea_1 este zerourile inițiale de inserat înaintea numărului, iar Valoarea_2 este numărul.

De exemplu, pentru a adăuga doar două zerouri înaintea unui număr, utilizați oricare dintre aceste formule:

=CONCATENATE("00",A2)

Primul argument este două zerouri („00”) deoarece dorim să adăugăm două zerouri înaintea numărului din A2 (care este al doilea argument).

Sau,

="00"&A2

Aici, primul argument este 2 zerouri, urmate de operatorul „&”, iar al doilea argument este numărul.

După cum puteți vedea, formula adaugă doar două zerouri de început la toate numerele dintr-o coloană, indiferent de câte cifre conține numărul.

Ambele formule unesc un anumit număr de zerouri înaintea numerelor originale și le stochează ca șiruri de text.

Utilizarea funcției REPT/LEN

Dacă doriți să adăugați zerouri de început la datele numerice sau alfanumerice și să convertiți șirul în text, atunci utilizați funcția REPT. Funcția REPT este utilizată pentru a repeta un caracter(e) de un anumit număr de ori. Această funcție poate fi folosită și pentru a introduce numere fixe de zerouri înainte de număr.

=REPT(text, număr_ori)

Unde „text” este caracterul pe care vrem să-l repetăm ​​(în cazul nostru „0”) și argumentul „number_times” este numărul de ori în care dorim să repetăm ​​acel caracter.

De exemplu, pentru a genera cinci zerouri înaintea numerelor, formula ar arăta astfel:

=REPT(0,5)&A2

Ceea ce face formula este să repete 5 zerouri și să unească șirul numeric din A2 și să returneze rezultatul. Apoi, formula este aplicată celulei B2:B6 folosind mânerul de umplere.

Formula de mai sus adaugă un număr fix de zerouri înaintea numărului, dar lungimea totală a numărului variază în funcție de număr.

Dacă doriți să adăugați zerouri de început oriunde este necesar pentru a crea șiruri de caractere lungi (cu lungime fixă), puteți utiliza împreună funcțiile REPT și LEN.

Sintaxă:

=REPT(text, număr_ori-LEN(text))&celulă

De exemplu, pentru a adăuga zerouri prefixate la valoarea din A2 și a crea un șir lung de 5 caractere, încercați această formulă:

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

Aici, „LEN(A2)” obține lungimea totală a șirului/numerelor din celula A2. „5” este lungimea maximă a șirului/numerelor pe care ar trebui să o aibă celula. Și partea „REPT(0,5-LEN(A2))” adaugă numărul de zerouri scăzând lungimea șirului din A2 din numărul maxim de zerouri (5). Apoi, un număr de 0 este alăturat înaintea valorii lui A2 pentru a forma un șir de lungime fixă.

Folosind funcția RIGHT

O altă modalitate de a completa zerouri înaintea unui șir în Excel este utilizarea funcției DREAPTA.

Funcția RIGHT poate adăuga un număr de zerouri la începutul unui număr și poate extrage cele N caractere cele mai din dreapta din valoare.

Sintaxă:

= DREAPTA (text, num_care)

• text este celula sau valoarea din care doriți să extrageți caracterele.
• num_care este numărul de caractere de extras din text. Dacă acest argument nu este dat, atunci va fi extras doar primul caracter.

Pentru această metodă, concatenăm numărul maxim de zerouri cu referință de celulă care conține șirul în argumentul „text”.

Pentru a crea un număr de 6 cifre bazat pe șirul de numere din A cu zerouri înainte, încercați această formulă:

=DREAPTA ("0000000"&A2,6)

Primul argument (text) al formulei adaugă 7 zerouri la valoarea din A2 („0000000”&A2), apoi returnează cele 7 caractere din dreapta, ceea ce are ca rezultat unele zerouri de început.

Adăugarea de zerouri principale utilizând formatarea personalizată a numerelor

Dacă utilizați oricare dintre metodele de mai sus pentru a pune zerouri înaintea numerelor, veți obține întotdeauna un șir de text, nu un număr. Și nu vor fi de mare folos în calcule sau în formule numerice.

Cel mai bun mod de a adăuga zerouri inițiale în Excel este să aplicați o formatare personalizată a numerelor. Dacă adăugați zerouri înainte prin adăugarea unui format de număr personalizat la celulă, aceasta nu schimbă valoarea celulei, ci doar modul în care este afișată. Valoarea va rămâne în continuare ca număr, nu text.

Pentru a modifica formatarea numărului celulelor, urmați acești pași:

Selectați celula sau intervalul de celule în care doriți să afișați zerourile inițiale. Apoi, faceți clic dreapta oriunde în intervalul selectat și selectați opțiunea „Format celule” din meniul contextual. Sau apăsați tastele de comandă rapidă Ctrl + 1.

În fereastra Formatare celule, accesați fila „Număr” și selectați „Personalizat” sub opțiunile de categorie.

Introduceți numărul de zerouri în caseta „Tip:” pentru a specifica numărul total de cifre pe care doriți să le afișați într-o celulă. De exemplu, dacă doriți ca numărul să aibă 6 cifre, atunci introduceți „000000” ca cod de format personalizat. Apoi, faceți clic pe „OK” pentru a aplica.

Aceasta va afișa zerouri înaintea numerelor și, dacă numărul este mai mic de 6 cifre, va adăuga zero înaintea lui.

Numerele vor părea să aibă doar zerouri la început, în timp ce valoarea de bază va rămâne neschimbată. Dacă selectați o celulă cu formatare personalizată, aceasta vă va afișa numărul original în bara de formule

Există o mulțime de substituenți digitali pe care îi puteți folosi în formatul de număr personalizat. Dar există doar doi substituenți primari pe care îi puteți folosi pentru a adăuga zerouri de început în numere.

• 0 – Este substituentul cifrei care afișează zerouri suplimentare. Afișează cifrele forțate 0-9 indiferent dacă cifra este sau nu relevantă pentru valoare. De exemplu, dacă tastați 2.5 cu codul de format 000.00, va afișa 002.50.
• # – Este substituentul pentru cifre care afișează cifre opționale și nu include zerouri suplimentare. De exemplu, dacă tastați 123 cu codul de formatare 000#, va afișa 0123.

De asemenea, orice semn de punctuație sau alt caracter pe care îl includeți în codul de format va fi afișat așa cum este. Puteți folosi caractere precum cratima (-), virgulă (,), bară oblică (/) etc.

De exemplu, puteți formata numerele ca numere de telefon utilizând formatul personalizat.

Caseta de dialog Format cod în Format celule:

Rezultatul:

Să aplicăm acest cod de formatare în următorul exemplu:

##0000

După cum puteți vedea, „0” va adăuga zerouri suplimentare, în timp ce „#” nu adaugă zerouri nesemnificative:

De asemenea, puteți utiliza coduri de format predefinite în secțiunea „Formate speciale” din caseta de dialog Formatare celule pentru coduri poștale, numere de telefon și numere de securitate socială.

Următorul tabel arată numerele cu zerouri înainte în care diferitele coduri de format „speciale” sunt aplicate coloanelor diferite:

Eliminarea zerourilor de început în Excel

Acum, ați învățat cum să adăugați zerouri inițiale în Excel, să vedem cum să eliminați zerourile inițiale din numărul de șiruri. Uneori, atunci când importați date dintr-o sursă externă, numerele pot ajunge să aibă zerouri de prefix și să fie formatate ca text. În astfel de cazuri, trebuie să eliminați zerourile de început și să le convertiți înapoi în numere, astfel încât să le puteți utiliza în formule.

Există diferite moduri în care puteți elimina zerourile inițiale în Excel și le vom vedea unul câte unul.

Eliminați zerourile principale prin modificarea formatării celulei

Dacă zerouri înainte au fost adăugate prin formatarea personalizată a numerelor, atunci le puteți elimina cu ușurință schimbând formatul celulelor. Vă puteți da seama dacă celulele dvs. sunt formatate personalizat uitându-vă la bara de adrese (zerourile vor fi vizibile în celulă, nu în bara de adrese).

Pentru a elimina zerourile prefixate, selectați celulele cu zerourile inițiale, faceți clic pe caseta „Format număr” și selectați opțiunea de formatare „General” sau „Număr”.

Acum, zerourile de început au dispărut:

Ștergeți zerourile principale prin conversia textului în numere

Dacă zerourile tale de început au fost adăugate prin modificarea formatului celulei sau prin adăugarea de apostrofe înaintea numerelor sau adăugate automat la importul de date, cel mai simplu mod de a le converti în numere este utilizarea opțiunii de verificare a erorilor. Iată cum o faci:

Puteți utiliza această metodă dacă numerele sunt aliniate la stânga și celulele au un mic triunghi verde (un indicator de eroare) în colțul din stânga sus al celulelor. Aceasta înseamnă că numerele sunt formatate ca text.

Selectați acele celule și veți vedea un avertisment galben în partea din dreapta sus a selecției. Apoi, faceți clic pe opțiunea „Convertire în număr” din meniul drop-down.

Zerurile dvs. vor fi eliminate și numerele vor fi convertite înapoi în formatul numeric (aliniat la dreapta).

Eliminarea zerourilor de început prin Înmulțirea/împărțirea cu 1

O altă modalitate ușoară și cea mai bună de a elimina primul este înmulțirea sau împărțirea numerelor cu 1. Împărțirea sau înmulțirea valorii nu schimbă valoarea, pur și simplu convertește valoarea înapoi într-un număr și elimină zerourile de la început.

Pentru a face acest lucru, tastați formula din exemplul de mai jos într-o celulă și apăsați ENTER. Zerourile de început vor fi eliminate și șirul va fi convertit înapoi într-un număr.

Apoi, aplicați această formulă altor celule folosind mânerul de umplere.

Puteți obține aceleași rezultate folosind comanda „Lipire specială”. Iată cum:

Tastați valoarea numerică „1” într-o celulă (să spunem în B2) și copiați acea valoare.

Apoi, selectați celulele în care doriți să eliminați zerourile de început. Apoi, faceți clic dreapta pe selecție și apoi selectați opțiunea „Lipire specială”.

În caseta de dialog Lipire specială, sub Operație, alegeți opțiunea „Înmulțire” sau „Împărțire” și faceți clic pe „OK”.

Asta este, zerourile tale de început vor fi eliminate, lăsând șirurile ca numere.

Eliminați zerourile principale folosind o Formule

O altă modalitate ușoară de a șterge zerouri prefixate este prin utilizarea funcției VALOARE. Această metodă ar putea fi utilă dacă zerourile tale înainte au fost adăugate utilizând o altă formulă sau apostrof sau prin formatare personalizată.

=VALOARE(A1)

Argumentul formulei poate fi o valoare sau referința de celulă care are valoarea. Formula elimină zerourile de început și convertește valoarea din text în număr. Apoi aplicați formula în restul celulelor.

Uneori, poate doriți să eliminați zerourile de început, dar doriți să păstrați numerele în format text. În astfel de cazuri, trebuie să utilizați împreună funcțiile TEXT() și VALUE () astfel:

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

Funcția VALUE convertește valoarea din A1 în număr. Dar al doilea argument, „#” convertește valoarea înapoi în format text fără zerouri suplimentare. Ca rezultat, veți obține numerele fără zerouri de început, dar încă în format text (aliniat la stânga).

Eliminați zerourile inițiale folosind funcția Text în coloane din Excel

O altă modalitate de a elimina zerourile inițiale este să utilizați funcția Text în coloane din Excel.

Selectați intervalul de celule care au numerele cu zerouri de început.

Apoi, accesați fila „Date” și faceți clic pe butonul „Text în coloane” din grupul Instrumente de date.

Va apărea asistentul „Conversie text în coloane”. La pasul 1 din 3, selectați „Delimitat” și faceți clic pe „Următorul”.

La pasul 2 din 3, debifați toate delimitatoarele și faceți clic pe „Următorul”.

La pasul final, lăsați opțiunea Format de date pe coloană ca „General” și alegeți destinația (prima celulă a intervalului) unde doriți numerele fără zerouri de început. Apoi, faceți clic pe „Terminare”

Și veți obține numerele cu începutul eliminat într-o coloană separată, așa cum se arată mai jos.

Asta e tot.