Aquest tutorial cobreix els diferents mètodes per afegir o mantenir zeros inicials, així com eliminar zeros inicials a Excel.
Sempre que introduïu o importeu números amb un o més zeros a l'inici, com ara 000652, Excel elimina automàticament aquests zeros i només el nombre es mostra a les cel·les (652). Això es deu al fet que els zeros inicials no són necessaris per als càlculs i no compten.
Tanmateix, hi ha moments en què aquests zeros a l'inici són necessaris, com quan introduïu números d'identificació, números de telèfon, números de targeta de crèdit, codis de producte o codis postals, etc. Afortunadament, Excel ens ofereix diverses maneres d'afegir o mantenir zeros a l'inici. a les cèl·lules. En aquest article, us mostrarem les diferents maneres d'afegir o mantenir zeros inicials i eliminar zeros inicials.
Afegir zeros principals a Excel
Essencialment, hi ha 2 mètodes que podeu utilitzar per afegir zeros inicials: un, formateu el vostre número com a "Text"; dos, utilitzeu el format personalitzat per afegir zeros inicials. El mètode que voleu utilitzar pot dependre del que vulgueu fer amb el número.
És possible que vulgueu afegir un zero inicial quan introduïu números d'identificació únics, números de compte, números de seguretat social o codis postals, etc. Però, no utilitzareu aquests números per a càlculs ni en funcions, així que és millor convertir-los. números al text. Mai no sumaries ni promediaries els números de telèfon ni els números de compte.
Hi ha diverses maneres d'afegir o relleu zeros abans dels números formatant-los com a text:
- Canviar el format de la cel·la a Text
- Afegir un apòstrofe (‘)
- Utilitzant la funció TEXT
- Utilitzant la funció REPT/LEN
- Utilitzeu l'operador de la funció CONCATENAR/ampersand (&)
- Utilitzant la funció RIGHT
Canviar el format de la cel·la a text
Aquesta és una de les maneres més senzilles d'afegir zeros inicials als vostres números. Si només aneu a introduir números i voleu mantenir els zeros a l'inici mentre escriviu, aquest és el mètode adequat. Si canvieu el format de la cel·la de General o Número a Text, podeu forçar Excel a tractar els vostres números com a valors de text i qualsevol cosa que escriviu a la cel·la es mantindrà exactament igual. Així és com ho fas:
Seleccioneu les cel·les a les quals voleu afegir zeros inicials. Aneu a la pestanya "Inici", feu clic al desplegable "Format" del grup Números i seleccioneu "Text" a les opcions de format.
Ara, quan escriviu els vostres números, Excel no n'eliminarà cap zero inicial.
És possible que vegeu un petit triangle verd (indicador d'error) a la cantonada superior esquerra de la cel·la i, quan seleccioneu aquesta cel·la, us mostrarà un senyal d'advertència que indica que heu desat el número com a text.
Per eliminar el missatge d'error, seleccioneu les cel·les, feu clic al signe d'advertència i, a continuació, seleccioneu "Ignora l'error" de la llista.
També podeu escriure números de telèfon amb espai o guionet entre el nombre, Excel tractarà automàticament aquests números com a text.
Utilitzant Leading Apòstrof (')
Una altra manera d'afegir zeros inicials a Excel és afegir un apòstrof (') al començament del número. Això obligarà Excel a introduir un número com a text.
Només cal que escriviu un apòstrof abans de qualsevol nombre i premeu "Enter". Excel deixarà intactes els zeros inicials, però el (') no serà visible al full de treball tret que seleccioneu la cel·la.
Ús de la funció de text
El mètode anterior afegeix zeros als nombres a mesura que els escriviu, però si ja teniu una llista de nombres i voleu posar zeros inicials abans d'ells, la funció TEXT és el mètode adequat per a vosaltres. La funció TEXT us permet convertir números en cadenes de text mentre apliqueu un format personalitzat.
La sintaxi de la funció TEXT:
= TEXT (valor, format_text)On,
- valor - És el valor numèric que necessiteu per convertir a text i aplicar el format.
- format_text – és el format que voleu aplicar.
Amb la funció TEXT, podeu especificar quants dígits hauria de tenir el vostre número. Per exemple, si voleu que els vostres números tinguin 8 dígits, escriviu 8 zeros al segon argument de la funció: "00000000". Si teniu un nombre de 6 dígits en una cel·la, la funció afegirà manualment 2 zeros inicials i si teniu números de 2 dígits com el 56, la resta seran zeros (00000056).
Per exemple, per afegir zeros inicials i fer que els nombres tinguin 6 dígits, utilitzeu aquesta fórmula:
=TEXT(A2,"000000")Com que tenim 6 zeros al segon argument de la fórmula, la funció converteix la cadena numèrica en una cadena de text i afegeix 5 zeros inicials per fer que la cadena tingui 6 dígits.
Nota: Recordeu incloure els codis de format entre cometes dobles a la funció.
Ara podeu aplicar la mateixa fórmula a la resta de cel·les arrossegant el mànec d'emplenament. Com podeu veure, la funció converteix els números en textos i afegeix zeros inicials als nombres de manera que el nombre total de dígits sigui 6.
La funció TEXT sempre retornarà el valor com a cadena de text, no com a nombre, de manera que no els podreu utilitzar en càlculs aritmètics, però encara podeu utilitzar-los en fórmules de cerca com ara BUSCAR V o INDEX/COINCIDENT per obtenir els detalls d'un producte amb identificadors de producte.
Ús de la funció CONCATENAR/operador i comercial (&)
Si voleu afegir un nombre fix de zeros a l'inici abans de tots els números d'una columna, podeu utilitzar la funció CONCATENAR o l'operador (&).
Sintaxi de la funció CONCATENAR:
=CONCATENAR(text1, [text2], ...)On,
text 1 - El nombre de zeros que cal inserir abans del nombre.
text2 - El número original o la referència de la cel·la
Sintaxi de l'operador Ampersand:
=Valor_1 i Valor_2 On,
Valor_1 són els zeros inicials que cal inserir abans del número i Valor_2 és el nombre.
Per exemple, per afegir només dos zeros abans d'un nombre, utilitzeu qualsevol d'aquestes fórmules:
=CONCATENAR("00",A2)El primer argument són dos zeros ("00") perquè volem posar dos zeros abans del nombre d'A2 (que és el segon argument).
O,
="00"&A2Aquí, el primer argument són 2 zeros, seguits de l'operador "&", i el segon argument és el nombre.
Com podeu veure, la fórmula afegeix només dos zeros inicials a tots els números d'una columna, independentment de quants dígits conté el nombre.
Ambdues fórmules uneixen un cert nombre de zeros abans dels nombres originals i els emmagatzemen com a cadenes de text.
Utilitzant la funció REPT/LEN
Si voleu afegir zeros inicials a dades numèriques o alfanumèriques i convertir la cadena en text, feu servir la funció REPT. La funció REPT s'utilitza per repetir un(s) caràcter(s) un nombre determinat de vegades. Aquesta funció també es pot utilitzar per inserir nombres fixos de zeros inicials abans del nombre.
=REPT(text, nombre_vegades)On "text" és el caràcter que volem repetir (en el nostre cas "0") i l'argument "nombre_vegades" és el nombre de vegades que volem repetir aquest caràcter.
Per exemple, per generar cinc zeros abans dels nombres, la fórmula seria així:
=REPT(0,5)&A2El que fa la fórmula és repetir 5 zeros i unir la cadena numèrica a A2 i retornar el resultat. A continuació, la fórmula s'aplica a la cel·la B2:B6 mitjançant el mànec d'emplenament.
La fórmula anterior afegeix un nombre fix de zeros abans del nombre, però la longitud total del nombre varia en funció del nombre.
Si voleu afegir zeros inicials allà on sigui necessari per crear cadenes de caràcters llargues (longitud fixa), podeu utilitzar les funcions REPT i LEN juntes.
Sintaxi:
=REPT(text, nombre_veces-LEN(text))&cel·laPer exemple, per afegir zeros prefixats al valor d'A2 i fer una cadena llarga de 5 caràcters, proveu aquesta fórmula:
=REPT(0,5-LEN(A2))&A2Aquí, "LEN(A2)" obté la longitud total de la cadena/nombres de la cel·la A2. "5" és la longitud màxima de cadena/números que hauria de tenir la cel·la. I la part "REPT(0,5-LEN(A2))" afegeix el nombre de zeros restant la longitud de la cadena en A2 del nombre màxim de zeros (5). A continuació, s'uneixen un nombre de 0 abans del valor de A2 per fer una cadena de longitud fixa.
Utilitzant la funció RIGHT
Una altra manera d'afegir zeros davant d'una cadena a Excel és utilitzar la funció DRET.
La funció RIGHT pot afegir un nombre de zeros a l'inici d'un nombre i extreure els N caràcters més a la dreta del valor.
Sintaxi:
= DRETA (text, num_cars)- text és la cel·la o el valor del qual voleu extreure caràcters.
- nombre_caràters és el nombre de caràcters que cal extreure del text. Si no es dóna aquest argument, només s'extreurà el primer caràcter.
Per a aquest mètode, concatenem el nombre màxim de zeros amb una referència de cel·la que conté la cadena a l'argument "text".
Per crear un nombre de 6 dígits basat en la cadena numèrica de A amb zeros inicials, proveu aquesta fórmula:
=DRETA("0000000"&A2,6)El primer argument (text) de la fórmula afegeix 7 zeros al valor d'A2 ("0000000"&A2) i després retorna els 7 caràcters de l'extrem dret, la qual cosa dóna com a resultat alguns zeros inicials.
Afegir zeros principals mitjançant el format de nombre personalitzat
Si utilitzeu algun dels mètodes anteriors per posar zeros davant dels números, sempre obtindreu una cadena de text, no un número. I no seran de gaire utilitat en càlculs ni en fórmules numèriques.
La millor manera d'afegir zeros inicials a Excel és aplicar un format de nombre personalitzat. Si afegiu zeros a l'inici afegint un format de nombre personalitzat a la cel·la, no canvia el valor de la cel·la sinó només la forma en què es mostra. El valor es mantindrà com a número, no com a text.
Per canviar el format del número de les cel·les, seguiu aquests passos:
Seleccioneu la cel·la o l'interval de cel·les on voleu mostrar zeros inicials. A continuació, feu clic amb el botó dret a qualsevol lloc dins de l'interval seleccionat i seleccioneu l'opció "Format de cel·les" al menú contextual. O premeu les tecles de drecera Ctrl + 1.
A la finestra Format de cel·les, aneu a la pestanya "Nombre" i seleccioneu "Personalitzat" a les opcions de categoria.
Introduïu el nombre de zeros al quadre "Tipus:" per especificar el nombre total de dígits que voleu mostrar en una cel·la. Per exemple, si voleu que el número tingui 6 dígits, introduïu "000000" com a codi de format personalitzat. A continuació, feu clic a "D'acord" per aplicar.
Això mostrarà zeros al capdavant abans dels números i, si el nombre és inferior a 6 dígits, hi haurà zero abans.
Els números només semblaran tenir zeros a l'inici, mentre que el valor subjacent es mantindria sense canvis. Si seleccioneu una cel·la amb un format personalitzat, us mostrarà el número original a la barra de fórmules
Hi ha molts marcadors de posició digitals que podeu utilitzar en el vostre format de número personalitzat. Però només hi ha dos marcadors de posició principals que podeu utilitzar per afegir zeros a l'inici dels números.
- 0 – És el marcador de posició de dígit que mostra zeros addicionals. Mostra els dígits forçats del 0 al 9 tant si el dígit és rellevant per al valor com si no. Per exemple, si escriviu 2.5 amb el codi de format 000.00, mostrarà 002.50.
- # – És el marcador de posició de dígits que mostra dígits opcionals i no inclou zeros addicionals. Per exemple, si escriviu 123 amb el codi de format 000#, mostrarà 0123.
A més, qualsevol signe de puntuació o altre caràcter que inclogueu al codi de format es mostrarà tal com està. Podeu utilitzar caràcters com el guionet (-), la coma (,), la barra inclinada (/), etc.
Per exemple, també podeu formatar números com a números de telèfon mitjançant un format personalitzat.
El quadre de diàleg Format de codi a Format de cel·les:
El resultat:
Apliquem aquest codi de format a l'exemple següent:
##0000
Com podeu veure, "0" afegirà zeros addicionals mentre que "#" no afegeix zeros insignificants:
També podeu utilitzar codis de format predefinits a la secció "Formats especials" del quadre de diàleg Format de cel·les per als codis postals, números de telèfon i números de la seguretat social.
La taula següent mostra números amb zeros a l'inici on s'apliquen diferents codis de format "especial" a diferents columnes:
Eliminació de zeros principals a Excel
Ara, heu après com afegir zeros inicials a Excel, vegem com eliminar els zeros inicials del nombre de cadenes. De vegades, quan importeu dades d'una font externa, els números poden acabar tenint zeros de prefix i formatats com a text. En aquests casos, heu d'eliminar els zeros inicials i tornar-los a convertir en números, de manera que els podeu utilitzar en fórmules.
Hi ha diverses maneres d'eliminar els zeros inicials a Excel i els veurem un per un.
Elimineu els zeros principals canviant el format de la cel·la
Si s'han afegit zeros inicials mitjançant un format de nombre personalitzat, podeu eliminar-los fàcilment canviant el format de les cel·les. Podeu saber si les vostres cel·les tenen un format personalitzat mirant la barra d'adreces (els zeros seran visibles a la cel·la no a la barra d'adreces).
Per eliminar els zeros prefixats, seleccioneu les cel·les amb els zeros inicials, feu clic al quadre "Format de nombre" i seleccioneu l'opció de format "General" o "Número".
Ara, els zeros inicials han desaparegut:
Elimina els zeros principals convertint el text en números
Si els vostres zeros inicials s'han afegit canviant el format de la cel·la o afegint apòstrofs abans dels números o s'han afegit automàticament en importar dades, la manera més senzilla de convertir-los en números és mitjançant l'opció de verificació d'errors. Així és com ho fas:
Podeu utilitzar aquest mètode si els vostres números estan alineats a l'esquerra i les vostres cel·les tenen un petit triangle verd (un indicador d'error) a la cantonada superior esquerra de les cel·les. Això significa que els números tenen el format de text.
Seleccioneu aquestes cel·les i veureu un avís groc a la part superior dreta de la selecció. A continuació, feu clic a l'opció "Convertir a número" del menú desplegable.
Els vostres zeros s'eliminaran i els números es tornaran a convertir al format de nombre (alineat a la dreta).
Eliminació de zeros principals per Multiplicar/dividir per 1
Una altra manera fàcil i millor d'eliminar l'inici és multiplicant o dividint els nombres per 1. Dividir o multiplicar el valor no canvia el valor, simplement torna a convertir el valor en un nombre i elimina els zeros inicials.
Per fer-ho, escriviu la fórmula de l'exemple següent en una cel·la i premeu INTRO. S'eliminaran els zeros inicials i la cadena es tornarà a convertir en un nombre.
A continuació, apliqueu aquesta fórmula a altres cel·les mitjançant el mànec d'emplenament.
Podeu aconseguir els mateixos resultats mitjançant l'ordre "Enganxa especial". Així és com:
Escriviu el valor numèric "1" en una cel·la (per exemple, a B2) i copieu aquest valor.
A continuació, seleccioneu les cel·les a les quals voleu eliminar els zeros inicials. A continuació, feu clic amb el botó dret a la selecció i, a continuació, seleccioneu l'opció "Enganxa especial".
Al quadre de diàleg Enganxa especial, a Operació, trieu l'opció "Multiplicar" o "Dividir" i feu clic a "D'acord".
Això és tot, els vostres zeros inicials s'eliminaran deixant les cadenes com a números.
Elimineu els zeros principals mitjançant una fórmula
Una altra manera senzilla d'esborrar zeros prefixats és mitjançant la funció VALOR. Aquest mètode pot ser útil tant si els zeros inicials s'han afegit mitjançant una altra fórmula o apòstrofe o amb un format personalitzat.
=VALOR(A1)L'argument de la fórmula podria ser un valor o la referència de cel·la que té el valor. La fórmula elimina els zeros inicials i converteix el valor de text a nombre. A continuació, apliqueu la fórmula a la resta de cel·les.
De vegades, és possible que vulgueu eliminar els zeros inicials, però voleu mantenir els números en format de text. En aquests casos, heu d'utilitzar les funcions TEXT() i VALUE () juntes com aquesta:
=TEXT(VALOR(A1),"#")La funció VALOR converteix el valor d'A1 en nombre. Però el segon argument, '#' torna a convertir el valor en format de text sense cap zero addicional. Com a resultat, obtindreu els números sense cap zero inicial però encara en format de text (alineat a l'esquerra).
Elimineu els zeros principals mitjançant la funció Text a columnes d'Excel
Una altra manera d'eliminar els zeros inicials és utilitzar la funció Text a columnes d'Excel.
Seleccioneu l'interval de cel·les que tenen els números amb zeros inicials.
A continuació, aneu a la pestanya "Dades" i feu clic al botó "Text a columnes" al grup Eines de dades.
Apareixerà l'assistent "Converteix text en columnes". Al pas 1 de 3, seleccioneu "Delimitat" i feu clic a "Següent".
Al pas 2 de 3, desmarqueu tots els delimitadors i feu clic a "Següent".
Al pas final, deixeu l'opció Format de dades de columna com a "General" i trieu la destinació (la primera cel·la de l'interval) on voleu els vostres números sense zeros inicials. A continuació, feu clic a "Finalitzar"
I obtindràs els números amb el principi eliminat en una columna separada tal com es mostra a continuació.
Això és tot.