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

Anonim

Muchas veces obtengo datos mixtos del campo y del servidor para analizarlos. Estos datos suelen estar sucios, ya que la columna se mezcla 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.

Guión:
Entonces, un amigo nuestro 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 Excel Formula? Por ejemplo, 125EvenueStreet y LoveYou3000, etc. "

Para extraer texto, usamos DERECHA, IZQUIERDA, MEDIA 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 Izquierda y otra función. 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 la cadena:

= MIN (BÚSQUEDA ({0,1,2,3,4,5,6,7,8,9}, String_Ref & "0123456789")

Esto devolverá la posición del primer número.
Para el ejemplo anterior, escriba esta fórmula en cualquier celda.

= MIN (BÚSQUEDA ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789"))

Extraer parte de texto

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

Ahora, para obtener Texto, desde la izquierda solo necesitamos obtener 15-1 carácter de la cadena. Entonces usaremos
Función IZQUIERDA para extraer texto.
Fórmula para extraer texto de la izquierda

= IZQUIERDA (A5, MIN (BÚSQUEDA ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")) - 1)


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

Ahora, para obtener números, solo necesitamos obtener los caracteres numéricos del primer número encontrado. Entonces calculamos la longitud total de cuerda y reste la posición del primer número encontrado y sume 1 lo. Sencillo. Sí, suena complejo, es simple.
Fórmula para extraer números de la derecha

= DERECHA (A5, LEN (A5) -MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")) + 1)

Aquí solo obtuvimos la longitud total de la cadena usando la función LEN 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 cómo extraer texto con las funciones IZQUIERDA y DERECHA de Excel.

Entonces, la parte de la función IZQUIERDA y DERECHA es simple. La parte difícil es la parte MIN y SEARCH que nos da la posición del primer número encontrado. Entendamos 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 (SEARCH ({0,1,2,3,4,5,6,7,8,9}, String & "0123456789 ")
La función BUSCAR devuelve la posición de un texto en una cadena. La función SEARCH ("texto", "cadena") toma dos argumentos, primero el texto que desea buscar, segundo la cadena en la que desea buscar.

    • Aquí en SEARCH, en la posición del texto tenemos una matriz de números del 0 al 9. Y en la posición de la cadena tenemos una cadena que está concatenada con "0123456789" usando & operador. ¿Por qué? Te diré.
    • Cada elemento de la matriz {0,1,2,3,4,5,6,7,8,9} se buscará en la cadena dada y devolverá su posición en la cadena en forma de matriz en el mismo índice en la matriz.
    • Si no se encuentra algún valor, se producirá un error. Por lo tanto, toda fórmula resultará en 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 son al final, por lo tanto, no causarán ningún problema.
    • Ahora, la función MIN devuelve el valor más pequeño de la matriz devuelta por la función BUSCAR. Este valor más pequeño será el primer número de la cadena. Ahora, usando esta función NÚMERO e IZQUIERDA y DERECHA, podemos dividir el texto y las partes de la cadena.

Examinemos nuestro ejemplo. En A5 tenemos la cadena que tiene el nombre de la calle y el número de la casa. Necesitamos separarlos en diferentes celdas.
Primero veamos cómo obtuvimos nuestra posición del primer número en la cadena.

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

Ahora, como expliqué, la búsqueda 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?
Se buscará 0 en una 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. Consíguelo. 0 no es parte de Monta27.
El siguiente 1 se buscará en una cadena y tampoco es parte de la cadena original, y obtenemos la posición 9.
Se buscarán los 2 siguientes. Dado que es parte de la cadena original, obtenemos su índice como 6.
De manera similar, 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,17}). 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. Usamos este número para extraer texto y números usando las funciones IZQUIERDA y DERECHA.

Extraer número y texto de una cadena cuando el número está al principio de la cadena
En el ejemplo anterior, el número 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 ese número de caracteres desde la izquierda de String.
Entonces el método es = LEFT (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:

= SUM (LEN (cadena) -LEN (SUBSTITUTE (string, {"0", "1", "2", "3", "4", "5", "6", "7", "8" , "9"}, ""))

Aquí,

      • La función SUSTITUIR reemplazará cada número encontrado con “” (en blanco). Si se encuentra un número ti sustituido y se agregará una nueva cadena a la matriz, de lo contrario, la cadena original se agregará a la matriz. De esta forma, tendremos una matriz de 10 cadenas.
      • Ahora la función LEN devolverá la longitud de los caracteres en una matriz de esas cadenas.
      • Luego, de la longitud de las cadenas originales, restaremos la longitud de cada cadena devuelta por la función SUSTITUIR. Esto volverá a devolver una matriz.
      • Ahora SUM sumará todos estos números. Este es el recuento de números en cadena.

Extraer la parte numérica de la cadena

Ahora que sabemos la longitud de los números en cuerda, sustituiremos esta función en IZQUIERDA.
Dado que tenemos nuestra cadena un A11 nuestro:

Fórmula para extraer números de la IZQUIERDA

= IZQUIERDA (A11, SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", "7" , "8", "9"}, ""))))


Extraer parte de texto de la cadena

Como sabemos el número de números, podemos restarlo de la longitud total de la cadena para obtener alfabetos numéricos en la 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

= DERECHA (A11, LEN (A2) -SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6 "," 7 "," 8 "," 9 "}," "))))


Cómo funciona
La parte principal en ambas fórmulas es SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", " 7 "," 8 "," 9 "}," "))) que calcula la primera aparición de un número. Solo después de encontrar esto, podemos dividir el texto y el número usando la función IZQUIERDA. Entonces, entendamos esto.

      • SUSTITUIR (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""): Esta parte devuelve una matriz de cadena en A11 después de sustituir estos números con nada / espacio en blanco (“”). Para 27Monta devolverá {"27Monta", "27Monta", "7Monta", "27Monta", "27Monta", "27Monta", "27Monta", "2Monta", "27Monta", "27Monta"}.
      • LEN (SUSTITUTO (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ") )): Ahora la parte SUSTITUTO está envuelta por la función LEN. Esta longitud de retorno de los textos en una matriz devuelta por la función SUSTITUIR. Como resultado, tendremos {7,7,6,7,7,7,7,6,7,7}.
      • LEN (A11) -LEN (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}.
      • SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""))): Aquí usamos SUM 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 una cadena.

Ahora, usando esto, podemos extraer los textos y numerarlos 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 las funciones IZQUIERDA y DERECHA de manera adecuada.
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 se mezclan texto y números. Para dividir texto y números, utilice esta función definida por el usuario.

Sintaxis:

= SplitNumText (cadena, op)

Cuerda: La cadena que desea dividir.
Op: esto es booleano. Pasa 0 o falso para obtener parte de texto. Para la parte numérica, pase cierto o cualquier número mayor que 0.
Por ejemplo, si la cadena está en A20,
La fórmula para extraer números de una cadena es:

= SplitNumText (A20,1)

Y
La fórmula para extraer texto de una cadena es:

= SplitNumText (A20,0)

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

Función SplitNumText (str As String, op As Boolean) num = "" txt = "" For i = 1 To Len (str) If IsNumeric (Mid (str, i, 1)) Entonces num = num & Mid (str, i , 1) Else txt = txt & Mid (str, i, 1) End If Next i If op = True Then SplitNumText = num Else SplitNumText = txt End If End Función

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

En mi opinión, esta es la mejor manera de dividir números y texto de una cadena.
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 una mejor solución en la sección de comentarios a continuación. Siempre es divertido interactuar con los chicos.

Haga clic en el enlace de abajo para descargar el archivo de trabajo:

Dividir número y texto de una celda

Articulos populares:
50 atajos de Excel para aumentar su productividad
La función BUSCARV en Excel
CONTAR.SI en Excel 2016
Cómo usar la función SUMIF en Excel