Cómo dividir números y texto de una cadena en Excel
Muchas veces obtengo datos mixtos de campo y servidor para analizarlos. Estos datos suelen estar sucios, con columnas mezcladas con números y 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:
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 RIGHT, LEFT, MID y otras funciones de texto. Solo necesitamos saber la cantidad de textos a extraer. Y aquí haremos lo mismo primero.
Extraiga el número y el 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(SEARCH({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(SEARCH({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 link: / excel-text-extract-text-from-a-string-in-excel-using-excels-left-and-right-function [función IZQUIERDA para extraer texto.]
Fórmula para extraer texto de la izquierda
=LEFT(A5,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))-1)
Aquí acabamos de restar 1 de cualquier número devuelto por MIN (link: / text-excel-search-function [SEARCH] (\ {0,1,2,3,4,5,6,7,8,9 }, A5 y «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 la cuerda y restamos la posición del primer número encontrado y le sumamos 1. Sencillo. Sí, suena complejo, es simple.
Fórmula para extraer números de la derecha
=RIGHT(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 `link: / len-functio [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 //excel-text/extract-text-from-a-string-in-excel-using-excels-left-and-right-function.html[extraer texto usando 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 (link: / text-excel-search-function [SEARCH] (\ {0,1,2,3,4,5,6,7,8,9}, String & «0123456789»)
El link: / text-excel-search-function [SEARCH function] devuelve la posición de un texto en una cadena.
La función SEARCH (‘text’, ‘string’) toma dos argumentos, primero el texto desea buscar, en segundo lugar 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 el operador & *. ¿Por qué? Te lo 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 forma de matriz cadena en el mismo índice en la matriz.
-
Si no se encuentra algún valor, causará 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 la 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 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 su posición 9.
Se buscarán los 2 siguientes. Como 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
link: / statistics-excel-min-function [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(string)-LEN(SUBSTITUTE(string,{"0","1","2","3","4","5","6","7","8","9"},""))
Aquí, ** link: / excel-text-formulas-excel-substitute-function [SUBSTITUTE function] 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 SUBSTITUTE. Esto volverá a devolver una matriz.
Ahora SUM sumará todos estos números. Este es el recuento de números en cadena.
Extraer parte numérica de la cadena
Ahora que conocemos la longitud de los números en una cadena, sustituiremos esta función en IZQUIERDA.
Dado que tenemos nuestra cadena un A11 nuestro:
Fórmula para extraer números de IZQUIERDA
=LEFT(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 DERECHA
=RIGHT(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 texto y número usando la función IZQUIERDA. Así que 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 SUBSTITUTO está envuelta por la función LEN. Esta longitud de retorno de los textos en 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 (SUSTITUTE (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.
Use 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(string, op)
Cadena: la cadena que desea dividir.
Op: esto es booleano. Pase 0 o falso para obtener la parte de texto. Para la parte numérica, 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 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.
Function SplitNumText(str As String, op As Boolean) num = "" txt = "" For i = 1 To Len(str) If IsNumeric(Mid(str, i, 1)) Then 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 Function
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 o en la variable txt. Si el usuario pasa true para op, 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:
`link: /wp-content-uploads-2019-11-Split-Number-and-Text-from-A-Cell.xls [__ Split Number and Text from A Cell]
Artículos relacionados:
link: / mail-send-and-receive-in-vba-how-to-extract-domain-name-from-email-in-excel [Cómo extraer el nombre de dominio de EMail en Excel]
link: / excel-text-formulas-split-numbers-and-text-from-string-in-excel [Dividir números y texto desde una cadena en Excel]
Artículos populares:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-incrementa-your-productividad [50 accesos directos de Excel para aumentar su productividad]
enlace: / fórmulas-y-funciones-introducción-de-vlookup-function [La función VLOOKUP en Excel]
enlace: / tips-countif-in-microsoft-excel [COUNTIF en Excel 2016]
link: / excel-formula-and-function-excel-sumif-function [Cómo usar la función SUMIF en Excel]