Separe el nombre y el apellido en Excel (nombres divididos mediante fórmulas)

Ver video: nombres divididos en Excel (en nombre, segundo nombre y apellido)

Excel es una herramienta increíble cuando se trata de cortar y cortar datos de texto.

Hay tantas fórmulas útiles y funcionalidades que puede utilizar para trabajar con datos de texto en Excel.

Una de las preguntas más comunes que recibo sobre la manipulación de datos de texto es: "¿Cómo separar nombres y apellidos (o nombre, segundo nombre y apellidos) en Excel?“.

Hay un par de formas sencillas de dividir nombres en Excel. El método que elija dependerá de cómo estén estructurados sus datos y de si desea que el resultado sea estático o dinámico.

Así que comencemos y veamos diferentes formas de dividir nombres en Excel.

Dividir nombres usando texto en columnas

La funcionalidad Texto a columnas en Excel le permite dividir rápidamente valores de texto en celdas separadas en una fila.

Por ejemplo, suponga que tiene el conjunto de datos como se muestra a continuación y desea separar el nombre y apellido y obtenerlos en celdas separadas.

A continuación se muestran los pasos para separar el nombre y el apellido usando Text to Columns:

1. Seleccione todos los nombres en la columna (A2: A10 en este ejemplo)
2. Haga clic en la pestaña "Datos".
3. En el grupo "Herramientas de datos", haga clic en la opción "Texto en columnas".
4. Realice los siguientes cambios en el Asistente para convertir texto en columna:
   1. Paso 1 de 3: seleccione Delimitado (esto le permite usar el espacio como separador) y haga clic en Siguiente
   2. Paso 2 de 3: seleccione la opción Espacio y haga clic en Siguiente
   3. Paso 3 de 3: establezca B2 como celda de destino (de lo contrario, sobrescribirá los datos existentes)
5. Haga clic en Finalizar

Los pasos anteriores dividirían instantáneamente los nombres en nombre y apellido (con los nombres en la columna B y el apellido en la columna C).

Nota: En caso de que ya haya datos en las celdas (aquellas en las que se espera la salida de Texto a columnas), Excel le mostrará una advertencia para informarle que ya hay algunos datos en las celdas. Puede optar por sobrescribir los datos o cancelar Text to Columns y eliminarlo manualmente primero.

Una vez hecho esto, puede eliminar los datos del nombre completo si lo desea.

Algunas cosas que debe saber al usar Text to Columns para separar nombres y apellidos en Excel:

1. El resultado de esto es estático. Esto significa que en caso de que tenga nuevos datos o los datos originales tengan algunos cambios, debe hacer esto de nuevo para dividir los nombres.
2. Si solo desea el nombre o solo el apellido, puede omitir las columnas que no desea en el paso 3 del cuadro de diálogo "Asistente de texto a columna". Para hacer esto, seleccione la columna en la vista previa que desea omitir y luego seleccione la opción "No importar columna (omitir)".
3. En caso de que no especifique la celda de destino, Text to Column sobrescribirá la columna actual

La opción Texto en columnas es más adecuada cuando tiene datos consistentes (por ejemplo, todos los nombres tienen solo nombre y apellido o todos los nombres tienen nombre, segundo nombre y apellido).

En este ejemplo, le he mostrado cómo separar nombres que tienen espacio como delimitador. En caso de que el delimitador sea una coma o una combinación de coma y espacio, puede seguir utilizando los mismos pasos. En este caso, puede especificar el delimitador en el paso 2 de 3 del asistente. Ya existe una opción para usar la coma como delimitador, o puede seleccionar la opción "Otro" y especificar también un delimitador personalizado.

Si bien Text to Columns es una forma rápida y eficiente de dividir nombres, es adecuada solo cuando desea que la salida sea un resultado estático. En caso de que tenga un conjunto de datos que pueda expandirse o cambiar, es mejor usar fórmulas para separar los nombres.

Separe el nombre, el segundo nombre y los apellidos mediante fórmulas

Las fórmulas le permiten cortar y cortar los datos de texto y extraer lo que desee.

En esta sección, compartiré varias fórmulas que puede usar para separar los datos de nombres (según la estructura de sus datos).

A continuación se muestran las tres fórmulas que puede usar para separar el nombre, el segundo nombre y el apellido (se explican en detalle más adelante en las siguientes secciones).

Fórmula para obtener el nombre:

= IZQUIERDA (A2, BÚSQUEDA ("", A2) -1)

Fórmula para obtener el segundo nombre:

= MEDIO (A2, BÚSQUEDA ("", A2) + 1, BÚSQUEDA ("", SUSTITUIR (A2, "", "@", 1)) - BÚSQUEDA ("", A2))

Fórmula para obtener el apellido:

= DERECHA (A15, LEN (A15) -SEARCH ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", "")))))

Obtener el nombre

Suponga que tiene el conjunto de datos como se muestra a continuación y desea separar rápidamente el nombre en una celda y el apellido en una celda.

La siguiente fórmula le dará el nombre:

= IZQUIERDA (A2, BÚSQUEDA ("", A2) -1)

La fórmula anterior utiliza la función de BÚSQUEDA para obtener la posición del carácter de espacio entre el nombre y el apellido. La función IZQUIERDA luego usa este número de posición de espacio para extraer todo el texto anterior.

Este es un uso bastante sencillo de extraer una parte del valor del texto. Dado que todo lo que tenemos que hacer es identificar la posición del primer carácter de espacio, no importa si el nombre tiene algún segundo nombre o no. La fórmula anterior funcionará bien.

Ahora, avancemos un poco más con cada ejemplo.

Obtener el apellido

Supongamos que tiene el mismo conjunto de datos y esta vez necesita obtener el apellido.

La siguiente fórmula extraerá el apellido del conjunto de datos anterior:

= DERECHA (A2, LARGO (A2) -BÚSQUEDA ("", A2))

De nuevo, bastante sencillo.

Esta vez, primero encontramos la posición del carácter del espacio, que luego se usa para averiguar el número de caracteres que quedan después del espacio (que sería el apellido).

Esto se logra restando el valor de posición del carácter de espacio con el número total de caracteres en el nombre.

Este número luego se usa en la función DERECHA para buscar todos estos caracteres desde la derecha del nombre.

Si bien esta fórmula funciona muy bien cuando solo hay el nombre y el apellido, no funcionaría en caso de que también tenga un segundo nombre. Esto se debe a que solo contabilizamos un carácter de espacio (entre el nombre y el apellido). Tener un segundo nombre agrega más caracteres de espacio al nombre.

Para buscar el apellido cuando también tiene un segundo nombre, use la siguiente fórmula:

= DERECHA (A15, LEN (A15) -SEARCH ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", "")))))

Ahora, esto ha comenzado a volverse un poco complejo … ¿no es así?

Déjame explicarte cómo funciona esto.

La fórmula anterior primero encuentra el número total de caracteres de espacio en el nombre. Esto se hace obteniendo la longitud del nombre con y sin el carácter de espacio y luego restando el que no tiene espacio del que tiene espacio. Esto da el número total de caracteres de espacio.

Luego, la función SUSTITUIR se usa para reemplazar el último carácter de espacio con un símbolo "@" (puede usar cualquier símbolo, algo que es poco probable que ocurra como parte del nombre).

Una vez que el símbolo @ se ha sustituido en lugar del último carácter de espacio, puede encontrar fácilmente la posición de este símbolo @. Esto se hace usando la función BUSCAR.

Ahora todo lo que necesita hacer es extraer todos los caracteres a la derecha de este símbolo @. Esto se hace usando la función DERECHA.

Obtener el segundo nombre

Suponga que tiene el conjunto de datos como se muestra a continuación y desea extraer el segundo nombre.

La siguiente fórmula hará esto:

= MEDIO (A2, BÚSQUEDA ("", A2) + 1, BÚSQUEDA ("", SUSTITUIR (A2, "", "@", 1)) - BÚSQUEDA ("", A2))

La fórmula anterior utiliza la función MID, que le permite especificar una posición de inicio y el número de caracteres a extraer de esa posición.

La posición de inicio es fácil de encontrar usando la función de BÚSQUEDA.

La parte difícil es encontrar cuántos caracteres extraer después de esta posición de inicio. Para obtener esto, debe identificar cuántos caracteres hay desde la posición inicial hasta el último carácter de espacio.

Esto se puede hacer usando la función SUSTITUIR y reemplazar el último carácter de espacio con un símbolo "@". Una vez hecho esto, puede usar fácilmente la función BUSCAR para encontrar la posición de este último carácter de espacio.

Ahora que tiene la posición inicial y la posición del último espacio, puede buscar fácilmente el segundo nombre usando la función MID.

Uno de los beneficios de utilizar una fórmula para separar los nombres es que el resultado es dinámico. Entonces, en caso de que su conjunto de datos se expanda y se le agreguen más nombres o si algunos nombres cambian en el conjunto de datos original, no necesita preocuparse por los datos resultantes.

Nombres separados usando Buscar y Reemplazar

Me encanta la flexibilidad que viene con "Buscar y reemplazar", porque puede usar caracteres comodín en él.

Primero, déjame explicarte qué es un personaje comodín.

Un carácter comodín es algo que puede utilizar en lugar de cualquier texto. Por ejemplo, puede usar un símbolo de asterisco (*) y representará cualquier número de caracteres en Excel. Para darle un ejemplo, si quiero encontrar todos los nombres que comienzan con el alfabeto A, puedo usar A * en buscar y reemplazar. Esto buscará y seleccionará todas las celdas donde el nombre comienza con A.

Si aún no lo tiene claro, no se preocupe. Siga leyendo y los siguientes ejemplos dejarán en claro qué son los caracteres comodín y cómo usarlos para separar rápidamente los nombres (o cualquier valor de texto en Excel).

En todos los ejemplos cubiertos a continuación, asegúrese de crear una copia de seguridad del conjunto de datos. Buscar y reemplazar cambia los datos en los que se usa. Es mejor copiar y pegar los datos primero y luego usar Buscar y reemplazar en el conjunto de datos copiado.

Obtener el nombre

Suponga que tiene un conjunto de datos como se muestra a continuación y desea obtener solo el nombre.

A continuación se muestran los pasos para hacer esto:

1. Copie los datos del nombre en la Columna A y péguelos en la Columna B.
2. Con los datos de la Columna B seleccionados, haga clic en la pestaña Inicio
3. En el grupo Edición, haga clic en Buscar y seleccionar.
4. Haga clic en Reemplazar. Esto abrirá el cuadro de diálogo "Buscar y reemplazar".
5. En el cuadro de diálogo "Buscar y reemplazar", ingrese lo siguiente
   1. Encontrar qué: * (carácter de espacio seguido del símbolo de asterisco)
   2. Reemplazar con: deje esto en blanco
6. Haga clic en Reemplazar todo.

Los pasos anteriores le darían el nombre y eliminarían todo lo que sigue al nombre.

Esto funciona incluso si tiene nombres que tienen un segundo nombre.

Consejo profesional: El método abreviado de teclado para abrir el cuadro de diálogo Buscar y reemplazar es Control + H (mantenga presionada la tecla de control y luego presione la tecla H).

Obtener el apellido

Suponga que tiene un conjunto de datos como se muestra a continuación y desea obtener solo el apellido.

A continuación se muestran los pasos para hacer esto:

1. Copie los datos del nombre en la Columna A y péguelos en la Columna B.
2. Con los datos de la Columna B seleccionados, haga clic en la pestaña Inicio
3. En el grupo Edición, haga clic en Buscar y seleccionar.
4. Haga clic en Reemplazar. Esto abrirá el cuadro de diálogo "Buscar y reemplazar".
5. En el cuadro de diálogo "Buscar y reemplazar", ingrese lo siguiente
   1. Encontrar qué: * (símbolo de asterisco seguido de un carácter de espacio)
   2. Reemplazar con: deje esto en blanco
6. Haga clic en Reemplazar todo.

Los pasos anteriores le darían el apellido y eliminarían todo antes del nombre.

Esto funciona incluso si tiene nombres que tienen un segundo nombre.

Eliminar el segundo nombre

En caso de que solo desee deshacerse del segundo nombre y solo tenga el nombre y el apellido, puede hacerlo usando Buscar y reemplazar.

Suponga que tiene un conjunto de datos como se muestra a continuación y desea eliminar el segundo nombre de estos.

A continuación se muestran los pasos para hacer esto:

1. Copie los datos del nombre en la Columna A y péguelos en la Columna B.
2. Con los datos de la Columna B seleccionados, haga clic en la pestaña Inicio
3. En el grupo Edición, haga clic en Buscar y seleccionar.
4. Haga clic en Reemplazar. Esto abrirá el cuadro de diálogo "Buscar y reemplazar".
5. En el cuadro de diálogo "Buscar y reemplazar", ingrese lo siguiente
   1. Encontrar qué: * (carácter de espacio seguido del símbolo de asterisco seguido del carácter de espacio)
   2. Reemplazar con: (solo ponga un carácter de espacio aquí)
6. Haga clic en Reemplazar todo.

Los pasos anteriores eliminarían el segundo nombre de un nombre completo. En caso de que algunos nombres no tengan un segundo nombre, no se cambiarán.

Nombres separados usando Flash Fill

El relleno flash se introdujo en Excel 2013 y hace que sea realmente fácil modificar o limpiar un conjunto de datos de texto.

Y cuando se trata de separar datos de nombres, está en el callejón de Flash Fill.

Lo más importante que debe saber al usar Flash Fill es que se necesita un patrón que el flash fill pueda identificar. Una vez que haya identificado el patrón, lo ayudará fácilmente a dividir nombres en Excel (obtendrá más claridad sobre esto cuando revise algunos ejemplos a continuación).

Obtenga el nombre o apellido del nombre completo

Suponga que tiene un conjunto de datos como se muestra a continuación y desea obtener solo el nombre.

1. En la celda adyacente, escriba manualmente el primer nombre del nombre completo. En este ejemplo, escribiría Rick.
2. En la segunda celda, escriba manualmente el primer nombre del nombre de la celda adyacente. Mientras escribe, verá Flash Fill que le muestra una lista del nombre automáticamente (en gris).
3. Cuando vea los nombres en gris, eche un vistazo rápidamente para asegurarse de que muestre los nombres correctos. Si son correctos, presione la tecla Intro y Flash Fill automáticamente llenará el resto de las celdas con el nombre.

Flash Fill necesita que le dé un patrón que pueda seguir cuando le proporcione los datos modificados. En nuestro ejemplo, cuando escribe el nombre en la primera celda, Flash Fill no puede descifrar el patrón.

Pero tan pronto como comience a ingresar el nombre en la segunda celda, Flash Fill comprende el patrón y le muestra una sugerencia. Si la sugerencia es correcta, simplemente presione la tecla Intro.

Y si no es correcto, puede intentar ingresar manualmente en algunas celdas más y verificar si Flash Fill puede discernir el patrón o no.

A veces, es posible que no vea el patrón en gris (como se muestra en el paso 2 anterior). Si ese es el caso, siga los pasos a continuación para obtener el resultado de Flash Fill:

1. Ingrese el texto manualmente en dos celdas.
2. Seleccione ambas celdas
3. Coloque el cursor en la parte inferior derecha de la selección. Notarás que el cursor cambia a un ícono más
4. Haga doble clic en él (tecla izquierda del mouse). Esto llenará todas las celdas. En este momento, es probable que los resultados sean incorrectos y no sean los esperados.
5. En la parte inferior derecha de los datos resultantes, verá un pequeño icono de Autocompletar. Haga clic en este icono de Autocompletar
6. Haga clic en Relleno flash

Los pasos anteriores le darían el resultado de Flash Fill (según el patrón que ha deducido).

También puede utilizar Flash Fill para obtener el apellido o el segundo nombre. En las dos primeras celdas, ingrese el apellido (o el segundo nombre) y el relleno flash podrá entender el patrón

Reorganizar el nombre usando Flash Fill

Flash Fill es una herramienta inteligente y también puede descifrar patrones ligeramente complejos

Por ejemplo, suponga que tiene un conjunto de datos como se muestra a continuación y desea reorganizar el nombre de Rick Novak a Novak, Rick (donde el apellido viene primero seguido de una coma y luego el nombre).

A continuación se muestran los pasos para hacer esto:

1. En la celda adyacente, escriba manualmente Novak, Rick
2. En la segunda celda, escriba manualmente Connor, Susan. Mientras escribe, verá Flash Fill que le muestra una lista de los nombres en el mismo formato (en gris).
3. Cuando vea los nombres en gris, eche un vistazo rápidamente para asegurarse de que muestre los nombres correctos. Si son correctos, presione la tecla Intro y Flash Fill automáticamente llenará el resto de las celdas con los nombres en el mismo formato.

Elimine el segundo nombre (o simplemente obtenga el segundo nombre)

También puede usar Flash Fill para deshacerse del segundo nombre u obtener solo el segundo nombre.

Por ejemplo, suponga que tiene un conjunto de datos como se muestra a continuación y desea obtener solo el nombre y el apellido y no el segundo nombre.

A continuación se muestran los pasos para hacer esto:

1. En la celda adyacente, escriba manualmente Rick Novak
2. En la segunda celda, escriba manualmente Susan Connor. Mientras escribe, verá Flash Fill que le muestra una lista de los nombres en el mismo formato (en gris).
3. Cuando vea los nombres en gris, eche un vistazo rápidamente para asegurarse de que muestre los nombres correctos. Si son correctos, presione la tecla Intro y Flash Fill automáticamente llenará el resto de las celdas con los nombres sin el segundo nombre.

Del mismo modo, si solo desea obtener el segundo nombre, escriba el segundo nombre en las dos primeras celdas y use Flash Fill para obtener el segundo nombre de todos los nombres restantes.

Los ejemplos que se muestran en este tutorial usan nombres mientras manipulan los datos de texto. Puede utilizar los mismos conceptos para trabajar también con otros formatos de datos (como direcciones, nombres de productos, etc.)