El tutorial muestra cómo usar las funciones de subcadena en Excel para extraer texto de una celda, obtener una subcadena antes o después de un carácter específico, buscar celdas que contengan parte de una cadena y más.
Antes de comenzar a discutir diferentes técnicas para manipular subcadenas en Excel, tomemos un momento para definir el término para que podamos comenzar en la misma página. Asi que, que es una subcadena? Simplemente, es parte de una entrada de texto. Por ejemplo, si escribe algo como «AA-111» en una celda, lo llamaría una cadena alfanumérica y cualquier parte de la cadena, digamos «AA», sería una subcadena.
Aunque no existe la función de subcadena en Excel, existen tres funciones de texto (IZQUIERDA, DERECHA y MEDIO) para extraer una subcadena de una longitud determinada. Además, existen funciones FIND y SEARCH para obtener una subcadena antes o después de un carácter específico. Y, hay un puñado de otras funciones para realizar operaciones más complejas, como extraer números de una cadena, reemplazar una subcadena con otra, buscar coincidencias de texto parcial, etc. A continuación encontrará ejemplos de fórmulas para hacer todo esto y mucho más. .
Cómo extraer una subcadena de cierta longitud
Microsoft Excel proporciona tres funciones diferentes para extraer texto de una longitud específica de una celda. Dependiendo de dónde desee comenzar la extracción, use una de estas fórmulas:
- Función IZQUIERDA: para extraer una subcadena de la izquierda.
- Función DERECHA: para extraer texto de la derecha.
- Función MID: para extraer una subcadena del medio de una cadena de texto, comenzando en el punto que especifique.
Como es el caso con otras fórmulas, las funciones de subcadena de Excel son mejores para aprender de un ejemplo, así que veamos algunas.
Extraer subcadena desde el inicio de la cadena (IZQUIERDA)
Para extraer texto de la izquierda de una cadena, utilice la función IZQUIERDA de Excel:
IZQUIERDA(texto, [num_chars])
Donde texto es la dirección de la celda que contiene la cadena de origen y num_chars es la cantidad de caracteres que desea extraer.
Por ejemplo, para obtener los primeros 4 caracteres desde el principio de una cadena de texto, use esta fórmula:
=LEFT(A2,4)
Obtener subcadena desde el final de la cadena (DERECHA)
Para obtener una subcadena de la parte derecha de una cadena de texto, utilice la función DERECHA de Excel:
DERECHA(texto, [num_chars])
Por ejemplo, para obtener los últimos 4 caracteres del final de una cadena, use esta fórmula:
=RIGHT(A2,4)
Extraer texto de la mitad de la cadena (MID)
Si está buscando extraer una subcadena que comience en el medio de una cadena, en la posición que especifique, entonces MID es la función en la que puede confiar.
En comparación con las otras dos funciones de texto, MID tiene una sintaxis ligeramente diferente:
MID(texto, núm_inicio, núm_caracteres)
Además de text (la cadena de texto original) y num_chars (la cantidad de caracteres a extraer), también indica start_num (el punto de partida).
En nuestro conjunto de datos de muestra, para obtener tres caracteres desde el medio de una cadena que comienza con el sexto carácter, usa la siguiente fórmula:
=MID(A2,6,3)
Consejo. El resultado de las fórmulas Derecha, Izquierda y Medio siempre es texto, incluso cuando está extrayendo un número de una cadena de texto. Si desea operar sobre el resultado como un númeroluego ajuste su fórmula en la función VALOR de esta manera: =VALUE(MID(A2,6,3))
Extraer subcadena antes o después de un carácter dado
Como se muestra en los ejemplos anteriores, las funciones Left, Right y Mid funcionan muy bien con cadenas uniformes. Cuando se trata de cadenas de texto de longitud variable, se necesitarán manipulaciones más complejas.
Nota. En todos los ejemplos a continuación, usaremos el no distingue entre mayúsculas y minúsculas Función BUSCAR para obtener la posición de un personaje. si quieres un distingue mayúsculas y minúsculas fórmula, use la función FIND en su lugar.
Cómo extraer texto antes de un carácter específico
Para obtener una subcadena que preceda a un carácter dado, se deben hacer dos cosas: primero, determina la posición del carácter de interés y luego extrae todos los caracteres anteriores. Más precisamente, usa la función BUSCAR para encontrar la posición del carácter y resta 1 del resultado, porque no desea incluir el carácter en sí mismo en la salida. Y luego, envía el número devuelto directamente al argumento num_chars de la función IZQUIERDA:
IZQUIERDA(celda, BUSCAR(«char», celda)-1)
Por ejemplo, para extraer una subcadena antes del carácter de guión (-) de la celda A2, use esta fórmula:
=LEFT(A2, SEARCH("-",A2)-1)
No importa cuántos caracteres contenga su cadena de Excel, la fórmula solo extrae el texto antes del primer guión:
Cómo extraer texto después de carácter
Para obtener texto después de un carácter específico, utilice un enfoque ligeramente diferente: obtenga la posición del carácter con BUSCAR o ENCONTRAR, reste ese número de la longitud total de la cadena devuelta por la función LEN y extraiga esa cantidad de caracteres del final de la cuerda.
DERECHA(celda,LEN(celda)-BUSCAR(«char», celda))
En nuestro ejemplo, usaríamos la siguiente fórmula para extraer una subcadena después del primer guión:
=RIGHT(A2,LEN(A2)-SEARCH("-",A2))
Cómo extraer texto entre dos instancias de un carácter
Para obtener una subcadena entre dos ocurrencias de un determinado carácter, use la siguiente fórmula genérica:
MID(celda, BÚSQUEDA(«char», celda)+1, BÚSQUEDA («char», celda, BÚSQUEDA («char», celda)+1) – BÚSQUEDA («char», celda)-1)
Los dos primeros argumentos de esta fórmula MID son muy claros:
Texto es la celda que contiene la cadena de texto original.
Start_num (punto de inicio): una fórmula de BÚSQUEDA simple devuelve la posición del carácter deseado, al que agrega 1 porque desea comenzar la extracción con el siguiente carácter.
Num_chars (número de caracteres para extraer) es la parte más complicada:
- Primero, calcula la posición de la segunda aparición del carácter anidando una función de búsqueda dentro de otra.
- Después de eso, resta la posición de la primera ocurrencia de la posición de la segunda ocurrencia y resta 1 del resultado ya que no desea incluir el carácter delimitador en la subcadena resultante.
Por ejemplo, para extraer texto rodeado por dos guiones, usaría esta fórmula:
=MID(A2, SEARCH("-",A2) + 1, SEARCH("-",A2,SEARCH("-",A2)+1) - SEARCH("-",A2) - 1)
La siguiente captura de pantalla muestra el resultado:
Si desea extraer texto entre la 2.ª y la 3.ª o la 3.ª y 4.ª aparición del mismo carácter, puede utilizar una combinación de SUSTITUCIÓN DE BÚSQUEDA más compacta para obtener la posición del personajecomo se explica en Cómo encontrar la enésima aparición de un carácter en una cadena:
ENCONTRAR(CARÁCTER(1),SUSTITUIR(celda,carácter,CARÁCTER(1),Nésima aparición))
En nuestro caso, podríamos extraer una subcadena entre el segundo y el tercer guion con la siguiente fórmula:
=MID(A2, FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),2))+1, FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),3)) - FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),2))-1)
Cómo encontrar una subcadena en Excel
En situaciones en las que no desea extraer una subcadena y solo desea encontrar celdas que la contengan, use la función BUSCAR o ENCONTRAR como se muestra en los ejemplos anteriores, pero realice la búsqueda dentro de la función ESNUMERO. Si una celda contiene la subcadena, la función de búsqueda devuelve la posición del primer carácter y, siempre que ESNÚMERO obtenga algún número, devuelve VERDADERO. Si no se encuentra la subcadena, la búsqueda da como resultado un error, lo que obliga a ESNÚMERO a devolver FALSO.
ESNUMERO(BUSCAR(«subcadena», celda))
Supongamos que tiene una lista de códigos postales británicos en la columna A y desea encontrar aquellos que contienen la subcadena «1ZZ». Para hacerlo, usa esta fórmula:
=ISNUMBER(SEARCH("1zz", A2))
Los resultados serán algo similar a esto:
Si desea devolver su propio mensaje en lugar de los valores lógicos de VERDADERO y FALSO, anide la fórmula anterior en la función SI:
=IF(ISNUMBER(SEARCH("1zz", A2)), "Yes", "")
Si una celda contiene la subcadena, la fórmula devuelve «Sí», una cadena vacía («») de lo contrario:
Como recordará, la función de BÚSQUEDA de Excel es no distingue entre mayúsculas y minúsculas, por lo que lo usa cuando el carácter de mayúsculas y minúsculas no importa. Para que su fórmula distinga los caracteres en mayúsculas y minúsculas, opte por el distingue mayúsculas y minúsculas función ENCONTRAR.
Para obtener más información sobre cómo buscar texto y números en Excel, consulte Si la celda contiene ejemplos de fórmulas.
Cómo extraer texto de una celda con Ultimate Suite for Excel
Como acaba de ver, Microsoft Excel proporciona una variedad de funciones diferentes para trabajar con cadenas de texto. En caso de que no esté seguro de qué función se adapta mejor a sus necesidades, envíe el trabajo a nuestra Ultimate Suite for Excel. Con estas herramientas en el arsenal de Excel, solo tiene que ir a la pestaña Datos de Ablebits > Grupo de texto y hacer clic en Extracto:
Ahora, seleccione las celdas de origen y, independientemente de las cadenas complejas que contengan, la extracción de una subcadena se reduce a estas dos acciones simples:
- Especifique cuántos caracteres desea obtener desde el principio, el final o la mitad de la cadena; o elija extraer todo el texto antes o después de un carácter determinado.
- Haga clic en Insertar resultados. ¡Hecho!
Por ejemplo, para extraer los nombres de dominio de la lista de direcciones de correo electrónico, seleccione el botón de opción Todo después del texto y escriba @ en el cuadro junto a él. Para extraer los nombres de usuario, seleccione el botón de opción Todo antes del texto, como se muestra en la siguiente captura de pantalla.
Y obtendrás los siguientes resultados en un momento:
Además de la velocidad y la simplicidad, la herramienta Extraer texto tiene un valor adicional: lo ayudará a aprender las fórmulas de Excel en general y las funciones de subcadenas en particular. ¿Cómo? Al seleccionar el Insertar como fórmula casilla de verificación en la parte inferior del panel, se asegura de que los resultados se muestren como fórmulas, no como valores.
En este ejemplo, si selecciona las celdas B2 y C2, verá las siguientes fórmulas, respectivamente:
- Para extraer el nombre de usuario:
=IFERROR(LEFT(A2,SEARCH("@",A2)-1),"") - Para extraer dominio:
=IFERROR(RIGHT(A2, LEN(A2)- SEARCH("@",A2) - LEN("@") + 1),"")
¿Cuánto tiempo te llevaría descubrir estas fórmulas por tu cuenta? 😉
Dado que los resultados son fórmulas, las subcadenas extraídas se actualizarán automáticamente tan pronto como se realicen cambios en las cadenas originales. Cuando se agregan nuevas entradas a su conjunto de datos, puede copiar las fórmulas a otras celdas como de costumbre, sin tener que ejecutar la herramienta Extraer texto nuevamente.
Si tiene curiosidad por probar esto, así como muchas otras características útiles incluidas con Ultimate Suite for Excel, puede descargar la versión de evaluación.
Más fórmulas para subcadenas en Excel
En este tutorial, hemos demostrado algunas fórmulas clásicas de Excel para extraer texto de una cadena. Como comprenderá, puede haber variaciones casi infinitas de estos escenarios básicos. A continuación, encontrará algunos ejemplos de fórmulas más en los que las funciones de texto son útiles.
descargas disponibles
(archivo .xlsx)
Ultimate Suite – versión de prueba (archivo .zip)