Cómo agregar ceros iniciales en Excel: todo lo que necesita saber

Hay situaciones en las que necesita agregar ceros a la izquierda a un conjunto de datos en Excel. Este podría ser el caso si mantiene registros en Excel, como ID de empleado o ID de transacción.

Por ejemplo, es posible que desee obtener una apariencia coherente en su conjunto de datos como se muestra a continuación:

En este tutorial, aprenderá varias formas de agregar ceros a la izquierda en Excel:

• Conversión del formato a texto
• Uso de formato de número personalizado
• Usando la función de texto
• Uso de las funciones REPT / LEN
• Usando VBA

Cada uno de estos métodos tiene algunos méritos e inconvenientes (que se tratan en cada sección).

Veamos cómo funciona cada uno de estos.

Agregue ceros a la izquierda convirtiendo el formato a texto

Cuándo usar: Cuando tiene un pequeño conjunto de datos numéricos y planea realizar esta edición manualmente.

Suponga que tiene identificadores de empleado del departamento de marketing como se muestra a continuación y desea que estos identificadores se vean consistentes agregando ceros a la izquierda.

Entonces intente cambiar la identificación ingresando ceros a la izquierda (00001 en lugar de 1).

Pero para su sorpresa, Excel lo convierte de nuevo a 1.

Esto sucede porque Excel entiende que 00001 y 1 son los mismos números y deben seguir las mismas reglas de visualización.

Ahora, por más frustrante que pueda ser para usted, Excel tiene sus razones.

Por lo tanto, para hacer el trabajo sin doblar las reglas de Excel, tendrá que aprovechar el hecho de que esta regla no se aplica al formato de texto.

Entonces, esto es lo que debe hacer:

1. Seleccione las celdas en las que desea agregar manualmente ceros a la izquierda.
2. Vaya a Inicio → Grupo de números y seleccione Texto en el menú desplegable.

¡Eso es!

Ahora, cuando ingresa ceros a la izquierda manualmente, Excel lo cumpliría fácilmente.

Precaución: cuando convierte el formato a texto, algunas funciones de Excel no funcionarán correctamente. Por ejemplo, la función SUM / COUNT ignoraría la celda ya que está en formato de texto.

Agregar ceros a la izquierda mediante el formato de número personalizado

Cuándo usar: Cuando tiene un conjunto de datos numérico y desea que el resultado sea numérico (no texto).

Cuando muestra un número en un formato específico, no cambia el valor subyacente del número. Por ejemplo, puedo mostrar el número 1000 como 1000 o 1000 o 1000,00 o 001000 o 26-09-1902 (las fechas pares son números en el backend en Excel).

En todas las diferentes formas de mostrar el número, el valor del número nunca cambia. Lo único que cambia es la forma en que se muestra.

Para agregar ceros a la izquierda, podemos formatearlo para mostrarlo de esa manera, mientras que el valor subyacente permanecería sin cambios.

Estos son los pasos para usar esta técnica para agregar ceros a la izquierda en Excel:

1. Seleccione las celdas en las que desea agregar ceros a la izquierda.
2. Vaya a Inicio → Grupo de números y haga clic en el iniciador de diálogo (una pequeña flecha inclinada en la parte inferior derecha). Esto abrirá el cuadro de diálogo Formato de celdas. Alternativamente, también puede usar el atajo de teclado: Control + 1.
3. En el cuadro de diálogo Formato de celdas, dentro de la pestaña Número, seleccione Personalizado en la lista Categoría.
4. En el campo Tipo, ingrese 00000
5. Haga clic en Aceptar.

Hacer esto siempre mostrará todos los números como cinco dígitos, donde los ceros iniciales se agregan automáticamente si el número tiene menos de 5 dígitos. Entonces, 10 se convertiría en 00010 y 100 se convertiría en 00100.

En este caso, hemos utilizado seis ceros, pero si sus datos tienen números con más dígitos, debe utilizar el formato correspondiente.

Nota: esta técnica funcionaría solo para un conjunto de datos numéricos. En caso de que tenga ID de empleado como A1, A2, A3, etc., estos son texto y no cambiarán cuando aplique el formato personalizado como se muestra arriba.

Agregar ceros a la izquierda mediante la función TEXTO

Cuándo usar: Cuando desee que el resultado sea texto.

La función TEXTO le permite cambiar el valor al formato deseado.

Por ejemplo, si desea que 1 se muestre como 001, puede usar la función TEXTO para eso.

Sin embargo, recuerde que la función TEXTO cambiaría el formato y lo convertiría en TEXTO. Esto significa que cuando hace 1 como 001, Excel trata el nuevo resultado como texto con tres caracteres (como abc o xyz).

A continuación, se explica cómo agregar ceros a la izquierda usando la función TEXTO:

1. Si tiene los números en la columna A (digamos de A2: A100), seleccione B2: B100 e ingrese la siguiente fórmula:
   = TEXTO (A2, ”00000 ″)
2. Presione Control + Entrar para aplicar la fórmula a todas las celdas seleccionadas.

Esto mostrará todos los números como cinco dígitos, donde los ceros iniciales se agregan automáticamente si el número tiene menos de 5 dígitos.

Un beneficio de convertir datos en texto es que ahora puede usarlos en fórmulas de búsqueda como BUSCARV o ÍNDICE / COINCIDIR para obtener los detalles de un empleado usando su identificación de empleado.

Nota: esta técnica funcionaría solo para un conjunto de datos numéricos. En caso de que tenga ID de empleado como A1, A2, A3, etc., estos son texto y no cambiarían cuando aplique el formato personalizado como se muestra arriba.

Agregue ceros a la izquierda mediante las funciones REPT y LEN

Cuándo usar: Cuando tiene un conjunto de datos numérico / alfanumérico y desea que el resultado sea texto.

El inconveniente de usar la función TEXTO era que solo funcionaría con datos numéricos. Pero en caso de que tenga un conjunto de datos alfanuméricos (por ejemplo, A1, A2, A3, etc.), la función TEXTO fallará.

En tales casos, una combinación de la función REPT y LEN funciona.

Así es como se hace:

1. Si tiene los números en la columna A (digamos de A2: A100), seleccione B2: B100 e ingrese la siguiente fórmula:
   = REPETIR (0,5-LEN (A2)) y A2
2. Presione Control + Entrar para aplicar la fórmula a todas las celdas seleccionadas.

Esto haría que todos los números / cadenas tengan 5 caracteres con ceros a la izquierda donde sea necesario.

Así es como funciona esta fórmula:

• LEN (A2) da la longitud de la cadena / números en la celda.
• = REPT (0,5-LEN (A2)) daría el número de 0 que se debe agregar. Aquí he usado 5 en la fórmula, ya que esa era la longitud máxima de cadena / números en mi conjunto de datos. Puede cambiar esto de acuerdo con sus datos.
• = REPT (0,5-LEN (A2)) & A2 simplemente agregaría el número de ceros al valor de la celda. Por ejemplo, si el valor en la celda es 123, esto devolvería 00123.

Agregar ceros a la izquierda mediante la función personalizada (VBA)

Si agregar ceros a la izquierda en Excel es algo que debe hacer con bastante frecuencia, usar una función personalizada es una buena idea.

Aquí está el código VBA que creará una función simple para agregar ceros a la izquierda:

'Código de Sumit Bansal de http://trumpexcel.com Función AddLeadingZeroes (ref As Range, Length As Integer) Dim i As Integer Dim Resultado como cadena Dim StrLen As Integer StrLen = Len (ref) For i = 1 To Length If i <= StrLen Then Result = Result & Mid (ref, i, 1) Else Result = "0" & ​​Result End If Next i AddLeadingZeroes = Result End Función

Simplemente agregue este código a la ventana de código del módulo y podrá usarlo como cualquier otra función de la hoja de trabajo.

O cree un complemento para él y podrá compartirlo con sus colegas.