Excel tiene un conjunto de funciones de texto que pueden hacer maravillas. Puede hacer todo tipo de operaciones de corte de texto y dados 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 hay ninguna función de subcadena en Excel que pueda hacer esto fácilmente. Sin embargo, esto todavía se podría hacer utilizando fórmulas de texto, así como algunas otras características integradas de Excel.,
Este Tutorial cubre:
primero echemos un vistazo a algunas de las funciones de texto que usaremos en este tutorial.
Excel TEXT Functions
Excel tiene una gama de funciones de texto que harían que sea realmente fácil extraer 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 los números especificados de caracteres de la derecha de la cadena de texto.
- Función izquierda: extrae los números especificados de caracteres de la izquierda de la cadena de texto.,
- Función MID: extrae los números especificados de caracteres de la posición inicial especificada en una cadena de texto.
- Función FIND: encuentra 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.,
Extraiga una subcadena en Excel usando funciones
supongamos que tiene un conjunto de datos como se muestra a continuación:
estos son algunos identificadores de correo electrónico aleatorios (pero como superhéroes) (excepto los míos), y en los ejemplos a continuación, le mostraré cómo extraer el nombre de usuario y el nombre de dominio utilizando las funciones de texto en Excel.
ejemplo 1 – Extracción de nombres de usuario de ID de correo electrónico
mientras se usan funciones de texto, es importante identificar un patrón (si lo hay). Eso hace que sea muy fácil de 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:
=LEFT(A2,FIND("@",A2)-1)
la fórmula anterior utiliza la función izquierda para extraer el nombre de usuario identificando la posición del signo @ en el id. Esto se hace utilizando la función FIND, que devuelve la posición de la @.
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 LEFT para extraer 10 caracteres de la izquierda de la cadena (uno menos que el valor devuelto por la función LEFT).
Ejemplo 2-Extraer el nombre de dominio de los identificadores 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.,
esta es la fórmula que va a hacer esto:
=RIGHT(A2,LEN(A2)-FIND("@",A2))
En la fórmula anterior, usamos la misma lógica, pero el ajuste es para asegurarse de que están recibiendo la cadena correcta.
tomemos de nuevo el ejemplo de [email protected]. la función Buscar devuelve la posición del signo@, que es 11 en este caso. Ahora, necesitamos extraer todos los caracteres después de @. Así que identificamos la longitud total de la cadena y restamos el número de caracteres hasta el @. Nos da el número de caracteres que cubren el nombre de dominio a la derecha.,
ahora simplemente podemos usar la función correcta para obtener el nombre de dominio.
Ejemplo 3-extraer el nombre de dominio de los identificadores de correo electrónico (sin .com)
para extraer una subcadena de la mitad 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:
=MID(A2,FIND("@",A2)+1,FIND(".",A2)-FIND("@",A2)-1)
La función Excel mid 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 el número de caracteres entre el ‘@’ y el ‘.’
actualización: uno de los lectores William19 mencionó que la fórmula anterior no funcionaría en caso de que haya un punto(.) en el id de correo electrónico (por ejemplo, bruce.wayne @ batman.,com). Así que aquí está la fórmula para lidiar con estos casos:
=MID(A1,FIND("@",A1)+1,FIND(".",A1,FIND("@",A1))-FIND("@",A1)-1)
usar texto a columnas para extraer una subcadena en Excel
usar 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 puede no necesitar, entonces usar la función Texto a columnas podría ser una manera rápida y fácil de dividir el texto en subcadenas basadas en marcadores especificados.
Aquí está cómo hacer esto:
- seleccione las celdas donde tiene el texto.,
- Ir a Data –> Herramientas de Datos –> Columnas de Texto.
- En El Paso 1 del Asistente de texto a columna, seleccione delimitado y presione Siguiente.
- En El Paso 2, marque la otra opción e ingrese @ en el cuadro derecho a él. 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 los datos originales intactos, cambie esto 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 para batman y com), repita el mismo proceso con él.,
usar 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 increíbles en Excel.
See Also: Learn All about Wildcard Characters in 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 utilizando la funcionalidad Buscar y reemplazar:
- copie y pegue los datos originales., Dado que Find and Replace funciona y altera los datos sobre los que se aplica, lo mejor es tener una copia de seguridad de los datos originales.
- Seleccione los datos e ir a Casa –> Edición> Buscar & Select> Reemplazar (o utilice el método abreviado de teclado Ctrl + H).,
- En el cuadro de diálogo Buscar y reemplazar, ingrese lo siguiente:
- buscar lo 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:
Ejemplo 2-extrayendo el nombre de dominio de los identificadores de correo electrónico
Usando la misma lógica, puede modificar el criterio ‘Find what’ para obtener el nombre de dominio.,
Estos son los pasos:
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:
También le pueden gustar los siguientes tutoriales de Excel:
- Cómo contar celdas que contienen cadenas de texto.
- extraiga nombres de usuario de ID de correo electrónico en Excel .
- Funciones de Excel (ejemplos + vídeos).
- Obtenga más de buscar y reemplazar en Excel.
- Cómo poner en mayúscula la Primera Letra de una Cadena de Texto en Excel
- Cómo Extraer la Primera Palabra de una Cadena de Texto en Excel