Cómo dividir números y texto de cadena en Excel

Muchas veces obtengo datos mixtos del campo y del servidor para su análisis. Estos datos suelen estar sucios, teniendo una columna mezclada con el número y el texto. Mientras hago la limpieza de datos antes del análisis, separo números y texto en columnas separadas. En este artículo, te diré cómo puedes hacerlo.

Escenario:

Así que uno nuestro amigo en Exceltip.com hizo esta pregunta en la sección de comentarios. "¿Cómo separo los números que vienen antes de un texto y al final del texto usando la Fórmula de Excel? Por ejemplo 125EvenueStreet y LoveYou3000, etc.”

Para extraer texto, usamos DERECHA, IZQUIERDA, EXTRAE y otras funciones de texto. Solo necesitamos saber la cantidad de textos a extraer. Y aquí haremos lo mismo primero.

Extraer número y texto de una cadena cuando el número está al final de la cadena

Para el ejemplo anterior he preparado esta hoja. En la celda A2, tengo la cadena. En la celda B2, quiero la parte de texto y en C2 la parte numérica.

Entonces solo necesitamos saber la posición desde donde comienza el número. Luego usaremos la función Izquierda y otra. Entonces, para obtener la posición del primer número, usamos la siguiente fórmula genérica:

Fórmula genérica para obtener la posición del primer número en cadena:

Esto devolverá la posición del primer número.

Para el ejemplo anterior, escriba esta fórmula en cualquier celda.

Extraer parte de texto

Devolverá 15 ya que el primer número que se encuentra está en la posición 15 en Texto. Lo explicaré más tarde.

Ahora, para obtener Text , desde la izquierda solo necesitamos obtener 15-1 caracteres de la cadena. Así que vamos a utilizar

Función IZQUIERDA para extraer texto.

Fórmula para extraer texto de la izquierda

Aquí solo restamos 1 del número devuelto por MIN(HALLAR({0,1,2,3,4,5,6,7,8,9}, A5&"0123456789") ).

Extracto Número Parte

Ahora para obtener números, solo necesitamos obtener caracteres numéricos del primer número encontrado. Entonces calculamos la longitud total de la cadena y restamos la posición del primer número encontrado y le agregamos 1. Simple. Sí, es sólo un sonido complejo, es simple.

Fórmula para extraer números de la derecha

Aquí acabamos de obtener la longitud total de la cadena usando la función LARGO y luego restamos la posición del primer número encontrado y luego le agregamos 1. Esto nos da el número total de números. Obtenga más información aquí sobre la extracción de texto usando las funciones IZQUIERDA y DERECHA de Excel.

Así que la parte de la función IZQUIERDA y DERECHA es simple. La parte Tricky es MIN y HALLAR Part que nos da la posición del primer número encontrado. Vamos a entender eso.

Cómo funciona

Sabemos cómo funcionan las funciones IZQUIERDA y DERECHA. Exploraremos la parte principal de esta fórmula que obtiene la posición del primer número encontrado y que es: MIN(HALLAR({0,1,2,3,4,5,6,7,8,9}, String&"0123456789")

La función HALLAR devuelve la posición de un texto en cadena. La función HALLAR ('texto', 'cadena') toma dos argumentos, primero el texto que desea buscar, segundo la cadena en la que desea buscar.

Aquí en HALLAR, en la posición de texto tenemos una matriz de números del 0 al 9. Y en la posición de cadena tenemos una cadena que está concatenada con "0123456789" usando & operador. ¿Por qué? Te lo diré.

Cada elemento de la matriz {0,1,2,3,4,5,6,7,8,9} se buscará en una cadena dada y devolverá su posición en una cadena de forma de matriz con el mismo índice en la matriz.

Si no se encuentra algún valor, provocará un error. Por lo tanto, toda fórmula dará como resultado un error. Para evitar esto, concatenamos los números "0123456789" en el texto. Para que siempre encuentre cada número en una cadena. Estos números están al final, por lo tanto, no causará ningún problema.

Ahora la función MIN devuelve el valor más pequeño de la matriz devuelta por la función HALLAR. Este valor más pequeño será el primer número en cadena. Ahora usando esta función NUMBER e IZQUIERDA y DERECHA, podemos dividir las partes de texto y cadena.

Vamos a examinar nuestro ejemplo. En A5 tenemos la cadena que tiene el nombre de la calle y el número de casa. Necesitamos separarlos en diferentes celdas.

Primero veamos cómo obtuvimos nuestra posición de primer número en cadena.

MIN(HALLAR({0,1,2,3,4,5,6,7,8,9},A5&"0123456789")): esto se traducirá en MIN(HALLAR({0,1,2,3,4,5,6,7,8,9},”Monta270123456789”))

Ahora, como expliqué, HALLAR buscará cada número en la matriz {0,1,2,3,4,5,6,7,8,9} en Monta270123456789 y devolverá su posición en forma de matriz. La matriz devuelta será {8,9,6,11,12,13,14,7,16,17}. ¿Cómo?

0 se buscará en cadena. Se encuentra en la posición 8. Por lo tanto, nuestro primer elemento es 8. Tenga en cuenta que nuestro texto original tiene solo 7 caracteres. Conseguirlo. 0 no es parte de Monta27.

El siguiente 1 se buscará en cadena y tampoco es parte de la cadena original, y obtenemos su posición 9.

Siguiente 2 será buscado. Como es la parte de la cadena original, obtenemos su índice como 6.

Del mismo modo, cada elemento se encuentra en alguna posición.

Ahora esta matriz se pasa a la función MIN como MIN({8,9,6,11,12,13,14,7,16,17Now MIN devuelve el 6 que es la posición del primer número encontrado en el texto original.

Y la historia después de esto es bastante simple. Utilizamos este número extraer texto y números utilizando la función IZQUIERDA y DERECHA.

Extraer número y texto de una cadena cuando el número está en el comienzo de la cadena

En el ejemplo anterior, Number estaba al final de la cadena. ¿Cómo extraemos el número y el texto cuando el número está al principio?

He preparado una tabla similar a la anterior. Simplemente tiene número al principio.

Aquí usaremos una técnica diferente. Contaremos la longitud de los números (que es 2 aquí) y extraeremos esa cantidad de caracteres de la izquierda de String.

Entonces el método es =IZQUIERDA (cadena, recuento de números)

Para contar el número de caracteres, esta es la Fórmula.

Fórmula genérica para contar el número de números:

Por ejemplo, si no es necesario, póngase en contacto con nosotros para obtener más información."},""))

Aqui,

La función de sustitución reemplazará cada número encontrado con “” (en blanco). Si se encuentra un número sustituido y se agregará una nueva cadena a la matriz, se agregará otra cadena original a la matriz. De esta manera, tendremos una matriz de 10 cadenas.

Ahora la función LARGO devolverá la longitud de los caracteres en una matriz de esas cadenas.

Luego, a partir de la longitud de las cadenas originales, restaremos la longitud de cada cadena devuelta por la función SUBSTITUTE. Esto volverá a devolver una matriz.

Ahora SUMA agregará todos estos números. Este es el recuento de números en cadena.

Extraer parte de número de cadena

Ahora ya que sabemos la longitud de los números en cadena, vamos a sustituir esta función en IZQUIERDA.

Ya que tenemos nuestra cadena un A11 nuestra:

Fórmula para extraer números de la IZQUIERDA

Extraer parte de texto de una cadena

Como conocemos el número de números, podemos restarlo de la longitud total de la cadena para obtener alfabetos numéricos en cadena y luego usar la función correcta para extraer esa cantidad de caracteres de la derecha de la cadena.

Fórmula para extraer texto de la DERECHA

Cómo funciona

La parte principal en ambas fórmulas es SUMA(LARGO(A11)LARGO(SUSTITUTE(A11,{"0","1","2","3","4","5","6","7","8","9"},"The que calcula la primera ocurrencia de un número. Solo después de encontrar esto, podemos dividir texto y número usando la función IZQUIERDA. Así que vamos a entender esto.

SUSTITUTE(A11,{"0","1","2","3","4","5","6","7","8","9"},"S Esta parte devuelve una matriz de cadenas en A11 después de sustituir estos números con nada / en blanco (“”). Para 27Monta volverá {"27Monta","27Monta","7Monta","27Monta","27Monta","27Monta","27Monta","2Monta","27Monta","27Monta"}.

LARGO(SUSTITUIR(A11,{"0","1","2","3","4","5","6","7","8","9"},"")): Ahora el SUSTITUTO parte es envuelto por la función LARGO. Esta longitud de retorno de los textos en la matriz devuelta por la función SUBSTITUTE. Como resultado, tendremos {7,7,6,7,7,7,7,6,7,7}.

LARGO(A11)-LARGO(SUBSTITUTE(A11,{"0","1","2","3","4","5","6","7","8","9"},"")): Aquí estamos restando cada número devuelto por la parte anterior de la longitud de la cadena real. La longitud del texto original es 7. Por lo tanto tendremos {7-7,7-7,7-6,....}. Finalmente tendremos {0,0,1,0,0,0,0,1,0,0}.

SUMA(LARGO(A11)-LARGO(SUBSTITUTE(A11,{"0","1","2","3","4","5","6","7","8","9"},""))): Aquí usamos SUMA para sumar la matriz devuelta por la parte anterior de la función. Esto dará 2. Que es el número de números en cadena.

Ahora usando esto podemos extraer los textos y el número y dividirlos en diferentes celdas. Este método funcionará con ambos tipos de texto, cuando el número está al principio y cuando está al final. Solo necesita utilizar la función IZQUIERDA y DERECHA de manera apropiada.

Utilice la función SplitNumText para dividir números y textos de una cadena

Los métodos anteriores son un poco complejos y no son útiles cuando el texto y los números se mezclan. Para dividir texto y números, use esta función definida por el usuario.

Sintaxis:

¿Cómo puedo hacer esto?)

Cadena: la cadena que desea dividir.

Op: esto es booleano. Pase 0 o falso para obtener la parte de texto. Para la parte del número, pase verdadero o cualquier número mayor que 0.

Por ejemplo, si la cadena está en A20, entonces,

La fórmula para extraer números de la cadena es:

Y

La fórmula para extraer texto de la cadena es:

Copie el siguiente código en el módulo VBA para que la fórmula anterior funcione.

Función SplitNumText (str como cadena, op como booleano)
número = ""
También ""
Para i = 1 Para LARGO(str)
Si es numérico (EXTRAE (str, i, 1)) Entonces
¿Cómo puedo hacerlo?)
Otro
¿Cómo puedo hacerlo?)
Final Si
Siguiente i
Si op = Verdadero entonces
DividirNumText = num
Otro
DividirNumText = txt
Final Si
Función final

Este código simplemente verifica cada carácter en cadena, si es un número o no. Si es un número, entonces se almacena en la variable num else en la variable txt. Si el usuario pasa true para op, se devuelve num else txt .

Esta es la mejor manera de dividir el número y el texto de una cadena en mi opinión.

Puede descargar el libro de trabajo aquí si lo desea.

Así que sí chicos, estas son las formas de dividir texto y números en diferentes celdas. Avíseme si tiene alguna duda o alguna solución mejor en la sección de comentarios a continuación. Siempre es divertido interactuar con chicos.