Cómo agregar ceros a la izquierda en Excel

Este tutorial cubre los diferentes métodos para agregar o mantener ceros a la izquierda, así como eliminar ceros a la izquierda en Excel.

Siempre que ingrese o importe números con uno o más ceros a la izquierda, como 000652, Excel elimina automáticamente esos ceros, y solo el número en sí se muestra en las celdas (652). Esto se debe a que los ceros iniciales no son necesarios para los cálculos y no cuentan.

Sin embargo, hay ocasiones en las que esos ceros a la izquierda son necesarios, como cuando ingresa números de identificación, números de teléfono, números de tarjetas de crédito, códigos de producto o códigos postales, etc. Afortunadamente, Excel nos brinda varias formas de agregar o mantener ceros a la izquierda en celdas. En este artículo, le mostraremos las diferentes formas de agregar o mantener ceros a la izquierda y eliminar ceros a la izquierda.

Agregar ceros a la izquierda en Excel

Básicamente, hay 2 métodos que puede utilizar para agregar ceros a la izquierda: uno, formatee su número como "Texto"; dos, use un formato personalizado para agregar ceros a la izquierda. El método que desee utilizar puede depender de lo que desee hacer con el número.

Es posible que desee agregar un cero a la izquierda cuando ingrese números de identificación únicos, números de cuenta, números de seguro social o códigos postales, etc. Pero no usará estos números para cálculos o funciones, por lo que es mejor convertirlos números al texto. Nunca sumaría o promediaría números de teléfono o números de cuenta.

Hay varias formas de agregar o rellenar ceros antes de los números formateándolos como texto:

• Cambiar el formato de celda a Texto
• Agregar un apóstrofo (‘)
• Uso de la función TEXTO
• Uso de la función REPT / LEN
• Utilice la función CONCATENAR / el operador Ampersand (&)
• Usando la función DERECHA

Cambiar el formato de celda a texto

Esta es una de las formas más sencillas de agregar ceros a la izquierda a sus números. Si solo va a ingresar números y desea mantener ceros a la izquierda mientras escribe, este es el método para usted. Al cambiar el formato de celda de General o Número a Texto, puede obligar a Excel a tratar sus números como valores de texto y todo lo que escriba en la celda permanecerá exactamente igual. Así es como se hace:

Seleccione las celdas en las que desea agregar ceros a la izquierda. Vaya a la pestaña "Inicio", haga clic en el cuadro desplegable "Formato" en el grupo Números y seleccione "Texto" en las opciones de formato.

Ahora, cuando escriba sus números, Excel no eliminará ningún cero a la izquierda.

Es posible que vea un pequeño triángulo verde (indicador de error) en la esquina superior izquierda de la celda y cuando seleccione esa celda, le mostrará una señal de advertencia que indica que ha almacenado el número como texto.

Para eliminar el mensaje de error, seleccione la (s) celda (s), haga clic en el signo de advertencia y luego seleccione "Ignorar error" de la lista.

También puede escribir números de teléfono con espacios o guiones entre los números, Excel automáticamente tratará estos números como texto.

Utilizando Leading Apóstrofo (‘)

Otra forma de agregar ceros a la izquierda en Excel es agregar un apóstrofo (‘) al comienzo del número. Esto obligará a Excel a ingresar un número como texto.

Simplemente escribe un apóstrofo antes de cualquier número y presiona "Enter". Excel dejará intactos los ceros iniciales, pero el (‘) no estará visible en la hoja de trabajo a menos que seleccione la celda.

Usando la función de texto

El método anterior agrega ceros a los números a medida que los escribe, pero si ya tiene una lista de números y desea rellenar los ceros iniciales antes de ellos, entonces la función TEXTO es el método adecuado para usted. La función TEXTO le permite convertir números en cadenas de texto mientras aplica un formato personalizado.

La sintaxis de la función TEXT:

= TEXTO (valor, formato_texto)

Donde,

• valor - Es el valor numérico que necesita convertir a texto y aplicar formato.
• format_text - es el formato que desea aplicar.

Con la función TEXTO, puede especificar cuántos dígitos debe tener su número. Por ejemplo, si desea que sus números tengan 8 dígitos, escriba 8 ceros en el segundo argumento de la función: "00000000". Si tiene un número de 6 dígitos en una celda, entonces la función agregará manualmente 2 ceros a la izquierda y si tiene números de 2 dígitos como 56, el resto serán ceros (00000056).

Por ejemplo, para agregar ceros a la izquierda y hacer que los números tengan una longitud de 6 dígitos, use esta fórmula:

= TEXTO (A2, "000000")

Como tenemos 6 ceros en el segundo argumento de la fórmula, la función convierte la cadena de números en una cadena de texto y agrega 5 ceros a la izquierda para hacer que la cadena tenga 6 dígitos.

Nota: Recuerde incluir los códigos de formato entre comillas dobles en la función.

Ahora puede aplicar la misma fórmula al resto de las celdas arrastrando el controlador de relleno. Como puede ver, la función convierte los números en texto y agrega ceros a la izquierda a los números para que el número total de dígitos sea 6.

La función TEXT siempre devolverá el valor como una cadena de texto, no como un número, por lo que no podrá usarlos en cálculos aritméticos, pero aún puede usarlos en fórmulas de búsqueda como VLOOKUP o INDEX / MATCH para obtener los detalles de un producto utilizando ID de producto.

Uso de la función CONCATENAR / Ampersand Operador (&)

Si desea agregar un número fijo de ceros a la izquierda antes de todos los números en una columna, puede usar la función CONCATENAR o el operador comercial (&).

Sintaxis de la función CONCATENAR:

= CONCATENAR (texto1, [texto2], ...)

Donde,

texto 1 - El número de ceros que se insertarán antes del número.

texto2 - El número original o la referencia de celda.

Sintaxis del operador Ampersand:

= Valor_1 y Valor_2 

Donde,

Value_1 son los ceros a la izquierda para insertar antes del número y Value_2 es ​​el número.

Por ejemplo, para agregar solo dos ceros antes de un número, use cualquiera de estas fórmulas:

= CONCATENAR ("00", A2)

El primer argumento es dos ceros ("00") porque queremos rellenar dos ceros antes del número en A2 (que es el segundo argumento).

O,

= "00" y A2

Aquí, el primer argumento es 2 ceros, seguido del operador "&", y el segundo argumento es el número.

Como puede ver, la fórmula agrega solo dos ceros a la izquierda a todos los números en una columna, independientemente de cuántos dígitos contenga el número.

Ambas fórmulas unen un cierto número de ceros antes de los números originales y los almacenan como cadenas de texto.

Uso de la función REPT / LEN

Si desea agregar ceros a la izquierda a datos numéricos o alfanuméricos y convertir la cadena en texto, utilice la función REPT. La función REPT se utiliza para repetir un carácter (s) un número específico de veces. Esta función también se puede utilizar para insertar números fijos de ceros iniciales antes del número.

= REPETIR (texto, número_veces)

Donde "texto" es el carácter que queremos repetir (en nuestro caso "0") y el argumento "número_veces" es el número de veces que queremos repetir ese carácter.

Por ejemplo, para generar cinco ceros antes de los números, la fórmula se vería así:

= REPETIR (0,5) y A2

Lo que hace la fórmula es repetir 5 ceros y une la cadena de números en A2 y devuelve el resultado. Luego, la fórmula se aplica a la celda B2: B6 usando el controlador de relleno.

La fórmula anterior agrega un número fijo de ceros antes del número, pero la longitud total del número varía según el número.

Si desea agregar ceros a la izquierda donde sea necesario para crear cadenas de caracteres largas (longitud fija) específicas, puede usar las funciones REPT y LEN juntas.

Sintaxis:

= REPETIR (texto, número_ veces-LEN (texto)) & celda

Por ejemplo, para agregar ceros con prefijo al valor en A2 y hacer una cadena larga de 5 caracteres, pruebe esta fórmula:

= REPETIR (0,5-LEN (A2)) y A2

Aquí, "LEN (A2)" obtiene la longitud total de la cadena / números en la celda A2. "5" es la longitud máxima de cadena / números que debe tener la celda. Y la parte "REPT (0,5-LEN (A2))" suma el número de ceros restando la longitud de la cadena en A2 del número máximo de ceros (5). Luego, se une un número de ceros antes del valor de A2 para formar una cadena de longitud fija.

Uso de la función DERECHA

Otra forma de rellenar los ceros iniciales antes de una cadena en Excel es usar la función DERECHA.

La función DERECHA puede agregar una cantidad de ceros al comienzo de un número y extraer los N caracteres más a la derecha del valor.

Sintaxis:

= DERECHA (texto, num_chars)

• texto es la celda o el valor del que desea extraer caracteres.
• num_chars es el número de caracteres que se extraerán del texto. Si no se proporciona este argumento, solo se extraerá el primer carácter.

Para este método, concatenamos el número máximo de ceros con la referencia de celda que contiene la cadena en el argumento "texto".

Para crear un número de 6 dígitos basado en la cadena de números en A con ceros a la izquierda, pruebe esta fórmula:

= DERECHA ("0000000" y A2,6)

El primer argumento (texto) de la fórmula agrega 7 ceros al valor en A2 ("0000000" y A2), y luego devuelve los 7 caracteres más a la derecha, lo que da como resultado algunos ceros a la izquierda.

Adición de ceros iniciales con formato de número personalizado

Si utiliza cualquiera de los métodos anteriores para poner ceros delante de los números, siempre obtendrá una cadena de texto, no un número. Y no serán de mucha utilidad en cálculos o fórmulas numéricas.

La mejor manera de agregar ceros a la izquierda en Excel es aplicar un formato de número personalizado. Si agrega ceros a la izquierda agregando un formato de número personalizado a la celda, no cambia el valor de la celda, sino solo la forma en que se muestra. El valor seguirá siendo un número, no un texto.

Para cambiar el formato numérico de las celdas, siga estos pasos:

Seleccione la celda o el rango de celdas donde desea mostrar los ceros a la izquierda. Luego, haga clic con el botón derecho en cualquier lugar dentro del rango seleccionado y seleccione la opción "Formato de celdas" en el menú contextual. O presione las teclas de método abreviado Ctrl + 1.

En la ventana Formato de celdas, vaya a la pestaña "Número" y seleccione "Personalizado" en las opciones de Categoría.

Ingrese el número de ceros en el cuadro "Tipo:" para especificar el número total de dígitos que desea mostrar en una celda. Por ejemplo, si desea que el número tenga 6 dígitos, ingrese "000000" como el código de formato personalizado. Luego, haga clic en "Aceptar" para aplicar.

Esto mostrará ceros a la izquierda antes de los números y si el número tiene menos de 6 dígitos, rellena el cero antes.

Los números solo aparecerán con ceros a la izquierda, mientras que el valor subyacente permanecerá sin cambios. Si selecciona una celda con formato personalizado, le mostrará el número original en la barra de fórmulas

Hay muchos marcadores de posición digitales que puede usar en su formato de número personalizado. Pero solo hay dos marcadores de posición principales que puede usar para agregar ceros a la izquierda en los números.

• 0 – Es el marcador de posición de dígitos que muestra ceros adicionales. Muestra dígitos forzados del 0 al 9 independientemente de que el dígito sea relevante para el valor o no. Por ejemplo, si escribe 2.5 con el código de formato 000.00, mostrará 002.50.
• # – Es el marcador de posición de dígitos que muestra dígitos opcionales y no incluye ceros adicionales. Por ejemplo, si escribe 123 con el código de formato 000 #, mostrará 0123.

Además, cualquier signo de puntuación u otro carácter que incluya en el código de formato se mostrará tal cual. Puede utilizar caracteres como guión (-), coma (,), barra inclinada (/), etc.

Por ejemplo, también puede formatear números como números de teléfono utilizando un formato personalizado.

El código de formato en el cuadro de diálogo Formato de celdas:

El resultado:

Apliquemos este código de formato en el siguiente ejemplo:

##0000

Como puede ver, "0" agregará ceros adicionales, mientras que "#" no agrega ceros insignificantes:

También puede utilizar códigos de formato predefinidos en la sección "Formatos especiales" del cuadro de diálogo Formato de celdas para códigos postales, números de teléfono y números de la seguridad social.

La siguiente tabla muestra números con ceros a la izquierda donde se aplican diferentes códigos de formato "especial" a diferentes columnas:

Eliminar ceros a la izquierda en Excel

Ahora que ha aprendido a agregar ceros a la izquierda en Excel, veamos cómo eliminar los ceros a la izquierda del número de cadenas. A veces, cuando importa datos de una fuente externa, los números pueden terminar teniendo prefijo ceros y formateados como texto. En tales casos, debe eliminar los ceros iniciales y convertirlos nuevamente en números, para poder usarlos en fórmulas.

Hay varias formas de eliminar los ceros a la izquierda en Excel y los veremos uno por uno.

Elimine los ceros iniciales cambiando el formato de la celda

Si se agregaron ceros a la izquierda mediante un formato de número personalizado, puede eliminarlos fácilmente cambiando el formato de las celdas. Puede saber si sus celdas tienen un formato personalizado mirando la barra de direcciones (los ceros serán visibles en la celda, no en la barra de direcciones).

Para eliminar los ceros con prefijo, seleccione las celdas con los ceros iniciales, haga clic en el cuadro "Formato de número" y seleccione la opción de formato "General" o "Número".

Ahora, los ceros iniciales se han ido:

Eliminar los ceros iniciales convirtiendo el texto en números

Si los ceros a la izquierda se agregaron cambiando el formato de celda o agregando apóstrofos antes de los números o si se agregaron automáticamente al importar datos, la forma más fácil de convertirlos en números es mediante la opción de verificación de errores. Así es como se hace:

Puede usar este método si sus números están alineados a la izquierda y sus celdas tienen un pequeño triángulo verde (un indicador de error) en la esquina superior izquierda de las celdas. Esto significa que los números están formateados como texto.

Seleccione esas celdas y verá una advertencia amarilla en la parte superior derecha de la selección. Luego, haga clic en la opción "Convertir en número" del menú desplegable.

Se eliminarán los ceros y los números se volverán a convertir al formato numérico (alineado a la derecha).

Eliminar ceros iniciales mediante Multiplicar / dividir por 1

Otra forma fácil y mejor de eliminar el interlineado es multiplicando o dividiendo los números con 1. Dividir o multiplicar el valor no cambia el valor, simplemente convierte el valor de nuevo en un número y elimina los ceros iniciales.

Para hacer esto, escriba la fórmula en el siguiente ejemplo en una celda y presione ENTER. Los ceros iniciales se eliminarán y la cadena se volverá a convertir en un número.

Luego, aplique esta fórmula a otras celdas usando el controlador de relleno.

Puede lograr los mismos resultados utilizando el comando "Pegar especial". Así es cómo:

Escriba "1" valor numérico en una celda (digamos en B2) y copie ese valor.

A continuación, seleccione las celdas en las que desea eliminar los ceros iniciales. Luego, haga clic derecho en la selección y luego seleccione la opción "Pegado especial".

En el cuadro de diálogo Pegado especial, en Operación, elija la opción "Multiplicar" o "Dividir" y haga clic en "Aceptar".

Eso es todo, sus ceros iniciales se eliminarán dejando las cadenas como números.

Eliminar ceros a la izquierda mediante fórmulas

Otra forma fácil de eliminar los ceros con prefijo es utilizando la función VALOR. Este método podría ser útil si los ceros iniciales se agregaron mediante otra fórmula o apóstrofo o mediante un formato personalizado.

= VALOR (A1)

El argumento de la fórmula podría ser un valor o la referencia de celda que tiene el valor. La fórmula elimina los ceros iniciales y convierte el valor de texto en número. Luego aplica la fórmula al resto de las celdas.

A veces, es posible que desee eliminar los ceros iniciales, pero desea mantener los números en el formato de texto. En tales casos, debe usar las funciones TEXT () y VALUE () juntas de esta manera:

= TEXTO (VALOR (A1), "#")

La función VALOR convierte el valor de A1 en un número. Pero el segundo argumento, "#" vuelve a convertir el valor al formato de texto sin ceros adicionales. Como resultado, obtendría los números sin ceros a la izquierda, pero aún en el formato de texto (alineado a la izquierda).

Elimine los ceros iniciales con la función Texto a columnas de Excel

Otra forma más de eliminar los ceros a la izquierda es utilizar la función Texto a columnas de Excel.

Seleccione el rango de celdas que tienen los números con ceros a la izquierda.

A continuación, vaya a la pestaña "Datos" y haga clic en el botón "Texto a columnas" en el grupo Herramientas de datos.

Aparecerá el asistente "Convertir texto en columnas". En el paso 1 de 3, seleccione "Delimitado" y haga clic en "Siguiente".

En el paso 2 de 3, desmarque todos los delimitadores y haga clic en "Siguiente".

En el paso final, deje la opción Formato de datos de columna como "General" y elija el destino (la primera celda del rango) donde desea sus números sin ceros a la izquierda. Luego, haga clic en "Finalizar"

Y obtendrá los números con el liderazgo eliminado en una columna separada como se muestra a continuación.

Eso es todo.