Técnicas de subcadenas en Excel: extrae y da formato al texto

Extraer texto por posición con funciones de subcadenas en Excel

A menudo queremos extraer partes específicas de una cadena de texto (una palabra o una secuencia de caracteres) según su posición. Excel incluye varias funciones para ayudarte a extraer porciones concretas de texto. Las más habituales son LEFT(), RIGHT(), MID(), TEXTBEFORE() y TEXTAFTER(). Veámoslas una a una. 

Usar la función de subcadena LEFT() en Excel

Con la función LEFT() puedes extraer caracteres desde el inicio de una cadena de texto. Su sintaxis es:

=LEFT(text, [num_chars])

Donde: 

• text hace referencia a la celda que contiene la cadena original.

• num_chars indica cuántos caracteres se extraen. Por defecto es 1.

Veamos un ejemplo. Tengo unos códigos de producto y quiero extraer sus tres primeros caracteres. 

=LEFT(A2,3)

Obtén los tres primeros caracteres con LEFT(). Imagen del autor.

Usar la función de subcadena RIGHT() en Excel

La función RIGHT() extrae caracteres desde el final de una cadena de texto. Su sintaxis es:

=Right(text, [num_chars])

Donde: 

• text es la celda con el texto original.

• num_chars es el número de caracteres a extraer. Por defecto es 1.

Ejemplo: uso RIGHT() para extraer los tres últimos caracteres.

=RIGHT(A2, 3)

Obtén los tres últimos caracteres con RIGHT(). Imagen del autor.

Usar la función de subcadena MID() en Excel

La función MID() extrae caracteres del medio de una cadena de texto. Su sintaxis es:

=MID(text, start_num, num_chars)

Donde:

• text es la celda con el texto original.

• num_chars es el número de caracteres a extraer. Por defecto es 1.

• start_num es la posición inicial dentro de la cadena.

En este ejemplo uso MID() para extraer tres caracteres a partir del cuarto.

=MID(A2,4,3)

Obtén tres caracteres intermedios con MID(). Imagen del autor. 

Listo: =MID(A2,4,3) extrae tres caracteres del texto en A2 empezando en el cuarto.

Usar la función FIND() en Excel

La función FIND() localiza una subcadena dentro de un texto y devuelve la posición de su primer carácter. Sintaxis:

FIND(find_text, within_text, [start_num]) 

Donde:

• find_text es el texto a buscar.

• within_text es el texto donde se realiza la búsqueda.

• start_num indica desde qué posición empezar (por defecto 1).

Ejemplo: en la columna Text tengo DataCamp y quiero localizar Camp. Devuelve 5 porque Camp empieza en el quinto carácter.

=FIND("Camp", A2)

Usar FIND() para obtener la posición del texto. Imagen del autor.

Usar la función SUBSTITUTE() en Excel

La función SUBSTITUTE() reemplaza una subcadena específica por otra. Sintaxis:

 SUBSTITUTE(text, old_text, new_text, [instance_num])

Donde:

• text contiene el texto que quieres cambiar.

• old_text es el texto a reemplazar.

• new_text es el nuevo texto que lo sustituye.

• instance_num indica qué ocurrencia reemplazar. Si se omite, reemplaza todas.

Para entenderlo, veamos un conjunto de datos con delimitadores. La columna Text lista nombres y edades separados por punto y coma. Para limpiarlo, escribo:

=SUBSTITUTE(A2, ";" , ",")

Aquí:

• A2 es la celda con el texto original.

• ";" indica el texto antiguo que vamos a cambiar.

• "," es el nuevo texto por el que lo sustituimos.

Usar SUBSTITUTE() para reemplazar texto antiguo. Imagen del autor.

Y ya está. Al aplicar la fórmula en B2, Excel cambia el punto y coma por una coma.

Extraer texto por delimitador con funciones de subcadenas en Excel

Los delimitadores separan partes de texto, como un punto y coma o una coma. En Excel, puedes usar TEXTBEFORE() y TEXTAFTER() para extraer texto cuando hay delimitadores.  

Usar la función TEXTBEFORE() en Excel 

Como su nombre indica, TEXTBEFORE() extrae el texto situado antes de un carácter o subcadena. Su sintaxis es:

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Donde: 

• text es el texto original.

• delimiter suele ser una coma o un punto y coma.

• instance_num es la ocurrencia deseada del delimitador. Por defecto 1.

• match_mode indica si la búsqueda del delimitador distingue mayúsculas y minúsculas (TRUE, por defecto) o no (FALSE).

• match_end determina si el final del texto debe tratarse como delimitador. Si es TRUE, devuelve el texto original si no encuentra el delimitador.

• if_not_found define qué valor devolver si no se encuentra el delimitador.

Ejemplo: en la columna Text tengo datos de empleados con nombre, departamento y oficina. Quiero extraer solo nombres y departamentos.

=TEXTBEFORE(A2, ",", 2, 1, 1)

Usar TEXTBEFORE() para extraer datos. Imagen del autor 

Así funciona: 

• A2 contiene el texto del que extraer. 

• "," es el delimitador que separa los datos originales. 

• 2 indica que use la segunda aparición del delimitador.

• 1 ignora diferencias de mayúsculas/minúsculas.

• 1 (al final) devuelve el texto original si no hay delimitador.

Usar la función TEXTAFTER() en Excel

TEXTAFTER() es similar a TEXTBEFORE(), con la diferencia de que devuelve el texto que va después del delimitador. Su sintaxis es:

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Donde: 

• text es el texto original.

• delimiter es una coma o un punto y coma. 

• instance_num es la ocurrencia del delimitador. Por defecto 1.

• match_mode indica si la búsqueda distingue mayúsculas y minúsculas (TRUE, por defecto) o no (FALSE).

• match_end determina si el final del texto cuenta como delimitador. Si es TRUE, devuelve el texto original si no lo encuentra.

• if_not_found especifica un valor personalizado si no hay delimitador.

Aquí tengo una columna Text con datos de empleados (nombre, departamento y oficina). Quiero extraer las ubicaciones y los IDs. 

=TEXTAFTER(A2,",",2,1,1)

Usar TEXTAFTER() para extraer datos. Imagen del autor 

Así funciona: 

• A2 contiene el texto del que extraer. 

• "," es el delimitador que separa el texto original. 

• 2 usa la segunda aparición del delimitador. 

• 1 ignora mayúsculas/minúsculas.

• 1 (al final) devuelve el texto original si no hay delimitador.

Casos de uso específicos con subcadenas en Excel

Ahora que dominas lo básico, pasemos a métodos más avanzados. Te permitirán manipulaciones de texto más complejas y harán tu procesamiento de datos aún más eficiente. 

Gestionar nombres de pila con longitudes variables

Tengo un conjunto de datos con el nombre completo y quiero extraer solo el nombre.

Tabla con nombres completos. Imagen del autor.

Podrías pensar que es sencillo: usa LEFT() para obtener el nombre. Lo probé con:

=LEFT(A2,4)

Usar LEFT() para obtener el nombre. Imagen del autor.

Pero en lugar de obtener el nombre, devolvió los cuatro primeros caracteres porque fijé num_char en 4 (Jane tiene cuatro letras). Al copiar la fórmula, no funciona para los demás.

Necesito algo más flexible. Para extraer el nombre sin importar su longitud, combina LEFT() con FIND(). FIND() localiza el primer espacio (que separa nombre y apellido) y LEFT() extrae los caracteres hasta esa posición. 

=LEFT(A2,FIND(" ",A2)-1)

Combina FIND() y LEFT() para extraer el nombre. Imagen del autor.

Desglose de la fórmula:

• La celda A2 contiene el nombre completo.

• FIND(" ", A2, 1) encuentra la posición del primer espacio en A2.

• -1 elimina el espacio.

• LEFT(A2, ...) extrae los caracteres desde la izquierda.

Extraer el dominio de una dirección de email

Veamos si LEFT() y FIND() sirven en otro escenario. Tengo direcciones de correo electrónico de las que debo extraer el dominio.

Datos con direcciones de email. Imagen del autor.

Creo una columna Email Domain y en otra celda escribo esta fórmula, que copio al resto:

=RIGHT(A2, LEN(A2) - FIND("@",A2))

Extrae el dominio con RIGHT(), LEN() y FIND(). Imagen del autor.

Listo: ya tengo todos los dominios. Así obtiene Excel el resultado:

• FIND("@", A2) localiza la posición del símbolo @ en el email.

• LEN(A2) calcula la longitud total.

• LEN(A2) - FIND("@", A2) obtiene cuántos caracteres hay tras el @.

• Por último, RIGHT(A2, LEN(A2) - FIND("@", A2)) extrae ese número de caracteres desde la derecha.

Extraer códigos de producto combinando LEFT(), MID() y RIGHT()

Hasta ahora he explicado cómo funcionan LEFT(), MID() y RIGHT() por separado. Pero combinándolas puedes hacer mucho más: extraer distintas partes de una cadena según patrones específicos.

Aquí tengo una lista de códigos de producto y quiero extraer una sección concreta de cada uno. 

• De la primera sección, un carácter.
• De la segunda, los cuatro caracteres.
• De la última, los tres finales.

Lista de códigos de producto. Imagen del autor.

Para lograrlo, combino LEFT(), RIGHT() y MID() y escribo:

=LEFT(A2, 1) & "-"& MID(A2, FIND("-", A2) + 1, 4) & "-" & RIGHT(A2, 3)

Combina MID(), LEFT() y RIGHT() para extraer datos. Imagen del autor.

Como ves, la fórmula combinada extrae los caracteres que buscábamos de cada sección. Así:

• LEFT(A2, 1) extrae el primer carácter.

• MID(A2, FIND("-", A2) + 1, 4) encuentra el primer guion, avanza un carácter y extrae cuatro.

• "-" añade un guion.

• RIGHT(A2, 3) extrae los tres últimos caracteres.

• & concatena las partes. 

Usar SUBSTITUTE() con MID() para extracciones dinámicas 

Ahora veamos cómo combinar MID() con SUBSTITUTE() para extraer partes específicas y después reemplazar caracteres o subcadenas dentro de esa parte.

Por ejemplo, tengo Order Details de clientes y quiero sustituir el ID del pedido por Order confirmed.

Tabla con detalles de pedidos. Imagen del autor.

Uso esta fórmula:

=SUBSTITUTE(A2, MID(A2,1,19), "Order confirmed")

SUBSTITUTE() y MID() para reemplazar texto. Imagen del autor.

¡Listo! La fórmula extrae y reemplaza justo lo que necesitaba.

• MID(A2, 1, 19) extrae los 19 primeros caracteres de A2.

• SUBSTITUTE(text, old_text, new_text) reemplaza old_text por new_text.

Otro ejemplo: en la columna Product Detail quiero extraer la talla entre corchetes.

=SUBSTITUTE(MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1), "[", "")

Extraer la talla entre corchetes. Imagen del autor.

Hecho: extrae todos los detalles.

• FIND("[", A2) halla la posición del corchete de apertura.

• FIND("]", A2) halla la del corchete de cierre.

• MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1) extrae el texto entre ambos.

• SUBSTITUTE(...,"[", "") elimina el corchete de apertura del resultado.

Depurar entradas de datos

A veces los datos llegan desordenados, pero no tienes por qué usarlos así. Tengo una lista de números de teléfono en múltiples formatos y quiero estandarizarlos limpiando las incoherencias.

Números sin formato. Imagen del autor.

Para estandarizarlos, creo la columna Clean data. Escribo esta fórmula en la segunda celda y la copio al resto:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "-", ""), "(", ""), ")", ""), ".", "")

Aquí: 

• SUBSTITUTE(A2, "-", "") elimina los guiones.

• SUBSTITUTE(..., "(", "") quita paréntesis de apertura.

• SUBSTITUTE(..., ")", "") quita paréntesis de cierre.

• SUBSTITUTE(..., ".", "") elimina los puntos.

Esto transforma los distintos formatos en una cadena continua de dígitos, como ves abajo. Ya está limpio y listo para usar. Puedes dejarlo así si quieres.

Limpia los datos con SUBSTITUTE(). Imagen del autor.

Dar formato al texto para informes

Si quieres ir un paso más allá y dar formato tras la limpieza, también puedes recurrir a funciones de subcadenas.

Siguiendo el ejemplo anterior (teléfonos limpios), ahora quiero formatear los números para que parezcan teléfonos y no una ristra de dígitos. 

=TEXT(LEFT(B2, 3), "000") & "-" & TEXT(MID(B2, 4, 3), "000") & "-" & TEXT(RIGHT(B2, 4), "0000")

Formato estandarizado con varias subcadenas. Imagen del autor.

Hecho. Ya tengo todos los números con el formato deseado. Así funciona:

• LEFT(B2, 3) extrae los tres primeros dígitos.

• MID(B2, 4, 3) extrae los tres siguientes desde la cuarta posición.

• RIGHT(B2, 4) extrae los cuatro últimos.

• TEXT(..., "000") y TEXT(..., "0000") dan formato asegurando el número de dígitos, con ceros a la izquierda si hace falta.

• & "-" & une las secciones con guiones.

Conclusiones

Ya sabes usar funciones de subcadenas de Excel como LEFT(), RIGHT(), MID(), TEXTBEFORE() y TEXTAFTER() para manipular texto. Tanto si necesitas extraer partes concretas como si quieres limpiar datos desordenados, estas funciones te facilitan y agilizan el trabajo.

Pero siempre hay más que aprender en Excel. Si acabas de empezar, el curso Introduction to Excel es un gran siguiente paso. Y si quieres profundizar en análisis de datos, prueba Data Analysis in Excel, que encaja de maravilla con nuestra chuleta de Data Manipulation in Excel. Te ayudarán a construir una base sólida y llevar tus habilidades al siguiente nivel.