Excel tiene un conjunto de funciones de TEXTO que pueden hacer maravillas. Puede realizar todo tipo de operaciones de corte y corte de texto utilizando estas funciones.
Una de las tareas comunes para las personas que trabajan con datos de texto es extraer una subcadena en Excel (es decir, obtener psrt del texto de una celda).
Desafortunadamente, no existe una función de subcadena en Excel que pueda hacer esto fácilmente. Sin embargo, esto aún se puede hacer usando fórmulas de texto, así como algunas otras funciones integradas de Excel.
Primero, echemos un vistazo a algunas de las funciones de texto que usaremos en este tutorial.
Funciones de Excel TEXT
Excel tiene una variedad de funciones de texto que facilitarían mucho la extracción de una subcadena del texto original en Excel. Aquí están las funciones de texto de Excel que usaremos en este tutorial:
- Función DERECHA: extrae el número especificado de caracteres de la derecha de la cadena de texto.
- Función IZQUIERDA: extrae el número especificado de caracteres de la izquierda de la cadena de texto.
- Función MID: extrae el número especificado de caracteres de la posición inicial especificada en una cadena de texto.
- Función ENCONTRAR: busca la posición inicial del texto especificado en la cadena de texto.
- Función LEN: Devuelve el número de caracteres de la cadena de texto.
Extraer una subcadena en Excel usando funciones
Suponga que tiene un conjunto de datos como se muestra a continuación:
Estos son algunos ID de correo electrónico aleatorios (pero superhéroes) (excepto el mío), y en los ejemplos a continuación, le mostraré cómo extraer el nombre de usuario y el nombre de dominio usando las funciones de texto en Excel.
Ejemplo 1: extracción de nombres de usuario de ID de correo electrónico
Al usar las funciones de texto, es importante identificar un patrón (si lo hay). Eso hace que sea realmente fácil construir una fórmula. En el caso anterior, el patrón es el signo @ entre el nombre de usuario y el nombre de dominio, y lo usaremos como referencia para obtener los nombres de usuario.
Aquí está la fórmula para obtener el nombre de usuario:
= IZQUIERDA (A2, BUSCAR ("@", A2) -1)
La fórmula anterior utiliza la función IZQUIERDA para extraer el nombre de usuario identificando la posición del signo @ en la identificación. Esto se hace usando la función ENCONTRAR, que devuelve la posición de @.
Por ejemplo, en el caso de [email protected], FIND (“@”, A2) devolvería 11, que es su posición en la cadena de texto.
Ahora usamos la función IZQUIERDA para extraer 10 caracteres de la izquierda de la cadena (uno menos que el valor devuelto por la función IZQUIERDA).
Ejemplo 2: extracción del nombre de dominio de los ID de correo electrónico
La misma lógica utilizada en el ejemplo anterior se puede utilizar para obtener el nombre de dominio. Una pequeña diferencia aquí es que necesitamos extraer los caracteres de la derecha de la cadena de texto.
Aquí está la fórmula que hará esto:
= DERECHA (A2, LEN (A2) -FIND ("@", A2))
En la fórmula anterior, usamos la misma lógica, pero la ajustamos para asegurarnos de que estamos obteniendo la cadena correcta.
Tomemos nuevamente el ejemplo de [email protected]. La función ENCONTRAR devuelve la posición del signo @, que es 11 en este caso. Ahora, necesitamos extraer todos los caracteres después de @. Entonces identificamos la longitud total de la cadena y restamos el número de caracteres hasta la @. Nos da la cantidad de caracteres que cubren el nombre de dominio a la derecha.
Ahora podemos simplemente usar la función DERECHA para obtener el nombre de dominio.
Ejemplo 3: extracción del nombre de dominio de los ID de correo electrónico (sin .com)
Para extraer una subcadena del medio de una cadena de texto, debe identificar la posición del marcador justo antes y después de la subcadena.
Por ejemplo, en el siguiente ejemplo, para obtener el nombre de dominio sin la parte .com, el marcador sería @ (que está justo antes del nombre de dominio) y. (que está justo después).
Aquí está la fórmula que extraerá solo el nombre de dominio:
= MEDIO (A2, ENCONTRAR ("@", A2) + 1, ENCONTRAR (".", A2) -Buscar ("@", A2) -1)
La función MID de Excel extrae el número especificado de caracteres de la posición inicial especificada. En este ejemplo anterior, FIND (“@”, A2) +1 especifica la posición inicial (que está justo después de @), y FIND (“.”, A2) -FIND (“@”, A2) -1 identifica la número de caracteres entre el '@' y el '.‘
Actualizar: Uno de los lectores, William19, mencionó que la fórmula anterior no funcionaría en caso de que hubiera un punto (.) En la identificación del correo electrónico (por ejemplo, [email protected]). Entonces, aquí está la fórmula para lidiar con tales casos:
= MEDIO (A1, ENCONTRAR ("@", A1) + 1, ENCONTRAR (".", A1, ENCONTRAR ("@", A1)) - ENCONTRAR ("@", A1) -1)
Uso de texto en columnas para extraer una subcadena en Excel
El uso de funciones para extraer una subcadena en Excel tiene la ventaja de ser dinámico. Si cambia el texto original, la fórmula actualizará automáticamente los resultados.
Si esto es algo que quizás no necesite, entonces el uso de la función Texto a columnas podría ser una forma rápida y fácil de dividir el texto en subcadenas basadas en marcadores específicos.
He aquí cómo hacer esto:
- Selecciona las celdas donde tienes el texto.
- Vaya a Datos -> Herramientas de datos -> Texto a columnas.
- En el Paso 1 del Asistente de texto a columna, seleccione Delimitado y presione Siguiente.
- En el Paso 2, marque la opción Otro e ingrese @ en el cuadro a la derecha. Este será nuestro delimitador que Excel usaría para dividir el texto en subcadenas. Puede ver la vista previa de datos a continuación. Haga clic en Siguiente.
- En el paso 3, la configuración general funciona bien en este caso. Sin embargo, puede elegir un formato diferente si está dividiendo números / fechas. De forma predeterminada, la celda de destino es donde tiene los datos originales. Si desea mantener intactos los datos originales, cámbielo a otra celda.
- Haga clic en Finalizar.
Esto le dará instantáneamente dos conjuntos de subcadenas para cada ID de correo electrónico utilizado en este ejemplo.
Si desea dividir aún más el texto (por ejemplo, dividir batman.com en batman y com), repita el mismo proceso con él.
Uso de BUSCAR y REEMPLAZAR para extraer texto de una celda en Excel
BUSCAR y REEMPLAZAR puede ser una técnica poderosa cuando trabaja con texto en Excel. En los ejemplos a continuación, aprenderá a usar BUSCAR y REEMPLAZAR con caracteres comodín para hacer cosas asombrosas en Excel.
Ver también: Aprenda todo sobre los caracteres comodín en Excel.
Tomemos los mismos ejemplos de ID de correo electrónico.
Ejemplo 1: extracción de nombres de usuario de ID de correo electrónico
Estos son los pasos para extraer nombres de usuario de ID de correo electrónico mediante la función Buscar y reemplazar:
- Copie y pegue los datos originales. Dado que Buscar y reemplazar funciona y altera los datos en los que se aplica, es mejor tener una copia de seguridad de los datos originales.
- Seleccione los datos y vaya a Inicio -> Edición -> Buscar y seleccionar -> Reemplazar (o use el atajo de teclado Ctrl + H).
- En el cuadro de diálogo Buscar y reemplazar, ingrese lo siguiente:
- Encontrar que: @*
- Reemplazar con: (deje esto en blanco)
- Haga clic en Reemplazar todo.
Esto eliminará instantáneamente todo el texto antes de la @ en los ID de correo electrónico. Tendrá el resultado como se muestra a continuación:
¿Cómo funciona esto? - En el ejemplo anterior, hemos utilizado una combinación de @ y *. Un asterisco (*) es un carácter comodín que representa cualquier número de caracteres. Por lo tanto, @ * significaría una cadena de texto que comienza con @ y puede tener cualquier número de caracteres después. Por ejemplo, en [email protected], @ * sería @ batman.com. Cuando reemplazamos @ * con un espacio en blanco, elimina todos los caracteres después de @ (incluido @).
Ejemplo 2: extracción del nombre de dominio de los ID de correo electrónico
Con la misma lógica, puede modificar los criterios "Buscar qué" para obtener el nombre de dominio.
Estos son los pasos:
- Seleccione los datos.
- Vaya a Inicio -> Edición -> Buscar y seleccionar -> Reemplazar (o use el atajo de teclado Ctrl + H).
- En el cuadro de diálogo Buscar y reemplazar, ingrese lo siguiente:
- Encontrar que: *@
- Reemplazar con: (deje esto en blanco)
- Haga clic en Reemplazar todo.
Esto eliminará instantáneamente todo el texto antes de la @ en los ID de correo electrónico. Tendrá el resultado como se muestra a continuación:
