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, encuentre 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. Entonces, que es una subcadena? Es simplemente parte de una entrada de texto. Por ejemplo, si escribe algo como «AA-111» en una celda, la llamaría año cadena alfanuméricay cualquier parte de la cadena, digamos «AA», sería un subcadena.
Aunque no existe una función de subcadena en Excel, hay tres funciones de texto (IZQUIERDA, DERECHA y MEDIO) para extraer una subcadena de una longitud determinada. También hay funciones FIND y SEARCH para obtener una subcadena antes o después de un determinado carácter. 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, encontrar coincidencias de texto parcial, etc. A continuación encontrará fórmulas de muestra para hacer todo esto y más. .
Cómo extraer una subcadena de una longitud dada
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 especificado.
Al igual que con otras fórmulas, las funciones de subcadenas de Excel se aprenden mejor con un ejemplo, así que veamos algunas.
Extraer subcadena desde el principio de la cadena (IZQUIERDA)
Para extraer texto de la izquierda de una cadena, use la función IZQUIERDA de Excel:
IZQUIERDA(texto, [num_chars])
Dónde texto es la dirección de la celda que contiene la cadena de origen y cual_num es el número 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 del lado derecho de una cadena de texto, vaya con 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 desea extraer una subcadena que comienza 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, num_inicial, num_coches)
Otro que texto (la cadena de texto original) y cual_num (número de caracteres a extraer), también indique su núm_inicio (punto de partida).
En nuestro conjunto de datos de ejemplo, para obtener tres caracteres del medio de una cadena que comienza con el sexto carácter, use la siguiente fórmula:
=MID(A2,6,3)
Consejo. El resultado de las fórmulas Derecha, Izquierda y Medio siempre es texto, incluso cuando extrae un número de una cadena de texto. Si desea operar el resultado como un númeroluego envuelva la fórmula en la función VALOR de esta manera:
=VALUE(MID(A2,6,3))
Extraiga la subcadena antes o después de cierto carácter
Como se muestra en los ejemplos anteriores, las funciones izquierda, derecha y central funcionan bien con cadenas uniformes. Cuando se trate de cadenas de texto de longitud variable, se requerirán manipulaciones más complejas.
Nota. En todos los ejemplos siguientes, utilizaremos mayúsculas y minúsculas Función BUSCAR para obtener la posición de un personaje. si quieres uno distingue mayúsculas y minúsculas fórmula, use la función FIND en su lugar.
Cómo extraer texto antes de cierto carácter
Para obtener una subcadena que precede a un determinado carácter, se deben hacer dos cosas: primero, determinar la posición del carácter de interés y luego dibujar todos los caracteres antes de él. Específicamente, use la función BUSCAR para encontrar la posición del carácter y reste 1 del resultado, ya que no desea incluir el carácter en sí mismo en el resultado. Y luego, envíe el número devuelto directamente a cual_num argumento de la función IZQUIERDA:
IZQUIERDA(celúlaBUSCAR(«Chara«, celúla)-1)
Por ejemplo, para extraer una subcadena antes del carácter de guión (-) en la celda A2, use esta fórmula:
=LEFT(A2, SEARCH("-",A2)-1)
Independientemente de cuántos caracteres contenga su cadena de Excel, la fórmula solo extrae el texto antes del primer guión:
Cómo extraer texto por carácter
Para obtener texto después de cierto carácter, use 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 de el final de la misma la cuerda.
BIEN(celúla,LARGO(celúla)-BUSCAR(«Chara«, celúla))
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 carácter dado, use la siguiente fórmula genérica:
MEDIO(celúlaBUSCAR(«Chara«, celúla)+1, BUSCAR («Chara«, celúlaBUSCAR («carbonizarse», celúla)+1) – BUSCAR («Chara«, celúla)-1)
Los dos primeros argumentos de esta fórmula MID son claros:
Texto es la celda que contiene la cadena de texto original.
Núm_inicio (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.
cual nombre (número de caracteres a extraer) es la parte más difícil:
- Primero, determine 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, reste la posición de la primera ocurrencia de la posición de la segunda ocurrencia y reste 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 2cuando y 3calle o 3cuando y 4el ocurrencias del mismo carácter, puede usar una combinación más compacta BUSCAR SUSTITUTO para obtener la posición del personajecomo se explica en Cómo encontrar una ocurrencia Na de un carácter en una cadena:
ENCONTRAR(CARÁCTER(1),SUSTITUIR(celúla,personaje,CARÁCTER(1),Una apariencia na))
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 subcadenas 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 TALADRAR 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 tome cualquier 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«, celúla))
Supongamos que tiene una lista de códigos postales del Reino Unido en la columna A y desea encontrar los que contienen la subcadena «1ZZ». Para hacer esto, use esta fórmula:
=ISNUMBER(SEARCH("1zz", A2))
Los resultados se verán algo como esto:
Si desea devolver su propio mensaje en lugar de los valores booleanos de VERDADERO y FALSO, coloque 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 mayúsculas y minúsculas, por lo que lo usa cuando el carácter no importa. Para que la fórmula distinga entre mayúsculas y minúsculas, elija distingue mayúsculas y minúsculas La 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 ofrece varias funciones diferentes para trabajar con cadenas de texto. Si no está seguro de qué característica es mejor para sus necesidades, considere Ultimate Suite for Excel. Con estas herramientas en su arsenal de Excel, vaya a Datos de bits habilitados pestaña > Texto grupo y haga clic 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 determinado carácter.
- Hacer clic Insertar resultados. ¡Terminado!
Por ejemplo, para extraer nombres de dominio de su lista de direcciones de correo electrónico, seleccione Todo según el texto. botón de radio y escriba @ en el cuadro junto a él. Para extraer el nombre de usuario, seleccione Todo antes del texto botón de radio como se muestra en la captura de pantalla a continuación.
Y obtendrá los siguientes resultados en muy poco tiempo:
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? Seleccionando Ingresar como fórmula casilla de verificación en la parte inferior del panel, asegúrese 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 el dominio:
=IFERROR(RIGHT(A2, LEN(A2)- SEARCH("@",A2) - LEN("@") + 1),"")
¿Cuánto tiempo te llevaría descubrir estas fórmulas por ti mismo? 😉
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 volver a ejecutar la herramienta Extraer texto.
Si tiene curiosidad por probar esta y 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, demostré algunas fórmulas clásicas de Excel para extraer texto de una cadena. Como puede ver, puede haber variaciones casi infinitas en estos escenarios básicos. A continuación encontrará algunos ejemplos de fórmulas en las que las funciones de texto son útiles.
Descargas disponibles
Funciones de subcadena de Excel – libro de práctica (archivo .xlsx)
Ultimate Suite – versión de prueba (archivo .exe)
