Nueva cabecera del blog #ExcelPedroWave

Mi nueva cabecera

En 10 años prácticamente no ha cambiado la cabecera de este blog de Excel, por lo que he decidido darle un nuevo aspecto, añadiendo un mapamundi de relojes junto a mi foto y, ante todo, he cambiado el título.

¿Qué te parece la nueva cabecera?

Mi nueva imagen

Mi nueva imagen contiene dos imágenes:

• Un mapamundi con los relojes de dos ciudades:
• Zaragoza en España (mi ciudad), con huso horario: UTC+01:00
• Visakhapatnam en la India (ciudad de contacto de Chandoo.org), con huso horario IST (UTC +5:30). Fue el primer blog en el que me inspiré y comenté mis retos y dudas.
• Para saber más visita este enlace:
• Cómo generar interfaces gráficos dinámicamente | #ExcelPedroWave

• La foto de Pedro dentro de un archivo Excel, con círculos grandes como olas, y círculos pequeños como burbujas, para referirme a la palabra inglesa Wave:
• En español significa: ola; onda; ondular; ondear; flotar; mover; saludar; señalar. De todas esas formas comparto mis retos en Excel.
• Para saber más visita este enlace:
• Cómo hacer cálculos y gráficos animados | #ExcelPedroWave

Mi nuevo título

He cambiado el título del blog, con un nuevo logotipo para dejar atrás referencias al género, porque este blog va dirigido a todos los que les gusta Excel o quieren aprender más trucos, tanto chicas como chicos, por lo que he quitado del título la palabra: Guys (en español: tipos, tíos, chicos, individuos).

También he cambiado el título en inglés, pues la mayoría de mis seguidores hablan español, por un título con mi propia etiqueta:

Anterior título:      Pedro Wave for Excel Guys

Nuevo título:        #ExcelPedroWave

que sirve tanto en inglés como en español, y donde se hace referencia a:

• # (símbolo almohadilla o numeral)
• Excel (mi herramienta principal)
• Pedro (mi nombre)
• Wave (mi ola, mi onda, mi saludo)

Mi nueva etiqueta

El símbolo # (almohadilla) le viene como anillo al dedo al título del blog pues representa muchos de los datos que se manejan en Excel:

• Las celdas de Excel como dos segmentos de recta similares, paralelos y casi verticales, cruzados por otros dos similares, paralelos y horizontales.
• Los formatos de celdas personalizadas usan el signo de número (#) si se desean mostrar solo los dígitos significativos de un número. 
• Con funciones de matriz desbordadas es posible hacer referencia a todo el rango de desbordamiento, colocando el operador de rango desbordado (#) después del rango. Sólo aplicable en Excel para Microsoft 365.
• El signo de número y cardinal. El signo # significa No. o Nº.
• Se parece al tablero del juego tres en línea. Excel también es un tablero, dashboard en inglés.
• En ajedrez sirve para indicar mate, por ejemplo:
• Mate pastor: 1 e4 e5 2 Ac4 Ac5 3 Dh5 Cc6 4 Dxf7#
• Se asemeja al símbolo musical de sostenido ().
• Precede a códigos de colores en formato hexadecimal. Rojo: #FF0000 (RGB: 255,0,0)
• Es una tecla del teléfono que activa funciones especiales o códigos abreviados.
• Introduce comentarios en algunos lenguajes de programación (como Perl, Python y PHP).
• Reemplaza al signo de sostenido en el nombre del lenguaje de programación C# (Sharp significa sostenido en inglés).
• En los sistemas operativos tipo Unix, el símbolo # se usa para representar al superusuario o administrador del sistema.
• En Twitter sirve para indicar en qué hashtag se encuentra un tuit. También se usan hashtags en Facebook, en LinkedIn y en otras redes.

Todas estas son buenas razones para cambiar el título de este blog con la etiqueta de mi propio hashtag:  #ExcelPedroWave

Mis motivos del cambio

Un motivo es que el nombre del blog no había cambiado en 11 años y ya tocaba cambiarlo.

El otro motivo son las noticias de cambio de nombre de las compañías tecnológicas, que están poniendo de moda poner "metas" a sus nombres. Aunque pensándolo bien, las empresas siempre han puesto objetivos y metas, ¿verdad?

Entre las empresas que se ponen como "meta" que nos comuniquemos como avatares dentro de un metaverso están:

• Facebook cambia a Meta, para que nos veamos las caras virtualmente en el metaverso.
• Microsoft introducirá el año que viene el metaverso Mesh en Microsoft Teams: Introducing Microsoft Mesh | Here can be anywhere.

Pero el metaverso ya lleva mucho tiempo inventado en 2D, y los que vendrán seguro que no serán como cuando nuestros avatares se movían libremente por Second Life hace tantos años. ¡Qué tiempos aquellos! ¡Cuando nuestro avatar volaba de isla en isla para conocer gente de todo el mundo!

Algún día no hará falta tener avatares distintos para meternos en cada red social, con meternos con nuestro avatar personal en un único mundo virtual podremos movernos por toda la red, sin salir de ella ni para ir al servicio...

¿Cuánto tardaremos en ver a nuestro avatar usar Excel dentro del mundo 3D virtual?

Mis Redes Sociales

Desde hace un tiempo #ExcelPedroWave es el hashtag que uso en las Redes Sociales (RRSS) para identificar mis publicaciones, por lo que es el que voy a usar a partir de ahora para identificar y etiquetar el blog. Puedes encontrarme en las redes con ese hashtag:

• LinkedIn #ExcelPedroWave

• Facebook #‎ExcelPedroWave

• Instagram #ExcelPedroWave (@excelpedrowave)

• Twitter #ExcelPedroWave (@pedrowave1)

También puedes encontrar estos enlaces dentro del menú: MIS REDES

Mis metas

Mis metas SMART siguen siendo las mismas que los objetivos que me puse cuando publiqué el primer artículo del blog:

• eSpecíficas: mejorar el conocimiento de Excel y sus herramientas Power.
• Medibles: escribir un articulo en el blog por cada reto que voy superando.
• Asignables: el autor y los visitantes del blog aprenderán cada día un poco más de Excel y Power.
• Realistas: en casi todos los artículos se puede descargar una plantilla de ejemplo.
• Temporales: todos los artículos se pueden visitar en cualquier momento y lugar y, en un virtual futuro, puede que dentro de un metaverso bloguero.

Mis metas son seguir superando nuevos retos en Excel y compartirlos con mis seguidores, para sacarle todo el poder a esta excelente herramienta multiusos, tan usada y a la vez tan incomprendida, para así poder mejorar y ampliar nuestros conocimientos de Excel, descargando multitud de ejemplos del blog.

Mi vídeo de presentación

En este vídeo explico cómo he hecho la nueva cabecera del blog:

En este vídeo hago una presentación de la nueva cabecera del blog explicando que, al mismo tiempo que he hecho ese cambio, he aprovechado para hacer limpieza de algunos elementos del blog, como actualizar el menú superior y la barra lateral, y añadir el Traductor de Google con el que se puede traducir el blog a 108 idiomas.

¿Por qué no pruebas a traducir el blog a tu propio idioma?

Muchas gracias por visitar y seguir mi blog #ExcelPedroWave

Test de Excel con cálculo iterativo

Para probar los conocimientos de los usuarios de Excel, he preparado un Test de Excel hecho usando únicamente fórmulas con cálculo iterativo.

Si quieres saber cómo usar el cálculo iterativo lee estos dos documentos de Excel:

• Quitar o permitir una referencia circular (microsoft.com)
• Cambiar la precisión, la iteración o el cálculo de fórmulas en Excel (microsoft.com)

Si quieres saber cómo programar un cuestionario con cálculo iterativo tendrás que leer un artículo mío anterior, pues me he propuesto no explicar cómo he hecho este Test de Excel.

Este cuestionario de traducción de 9 idiomas te puede sugerir ideas del uso del cálculo iterativo:

• Traducción de idiomas con cálculo iterativo | Pedro Wave for Excel Guys

Presentación del Test de Excel

Este Test de Excel consta de 20 preguntas, siendo las respuestas libres o escribiendo los números ordenados de hasta 4 posibles respuestas.

Las 5 hojas del Test de Excel se pueden ver en esta imagen animada:

Hojas del Test de Excel

Este Test de Excel consta de 5 hojas:

• Test: Es la hoja principal del Test, donde van apareciendo 20 preguntas aleatorias, con hasta 4 posibles respuestas, y donde se responde al Test y se ven los tiempos totales y parciales y si la respuesta es correcta o no.

• Resultados: Muestra una tabla con los resultados parciales o finales del Test, ocultando las respuestas correctas para despistar al usuario.

• Configuración: Con el número de preguntas, tipo de pregunta, tiempos, filas y contadores auxiliares. No se puede cambiar la configuración en esta versión del Test.

• Preguntas: Con la tabla de preguntas del Test.

• Respuestas: Con la tabla de respuestas del Test, cifradas para que no se vea anticipadamente la respuesta a cada pregunta...

Cómo responder al Test de Excel

Las respuestas al Test de Excel dependen del tipo de preguntas:

• Preguntas con respuesta sensible a mayúsculas y minúsculas: Se escribirá el texto con la respuesta y se presionará la tecla de Retorno.
• Por ejemplo, si la respuesta es la función EXTRAE, se debe escribir con mayúsculas para que la respuesta sea correcta. Si se escribe Extrae, la respuesta será considerada incorrecta.
• Para responder con instrucciones VBA, por ejemplo Mid es correcta pues se ha escrito la primera letra con mayúsculas y las demás con minúsculas. Si escribe la instrucción MID con todas las letras mayúsculas, la respuesta será considerada incorrecta.

• Preguntas con hasta 4 posibles respuestas:
• Si es correcta sólo una de las respuestas: se escribirá el número de la respuesta y se presionará la tecla de Retorno.
• Por ejemplo, si la segunda respuesta es la correcta se escribirá: 2
• Si son correctas varias de las respuestas: se escribirán los números ordenados de las respuestas y se presionará la tecla de Retorno.
• Por ejemplo, si la primera y tercera respuesta son correctas, se escribirá: 13
• Si en ese caso se contesta: 31, la respuesta será considerada incorrecta.

Test de Excel en la nube

Este Test de Excel lo he subido a la nube de Microsoft OneDrive y no lo vas a poder descargar, por lo que sólo puedes hacer el Test desde esta ventana:

Ventajas del Test de Excel en la nube

Como este Test de Excel está hecho con cálculo iterativo funciona igual de bien, tanto en la nube como si estuviera abierto en el Excel de escritorio.

Además tiene algunas ventajas por estar en la nube:

1. No contiene macros VBA ni Office Script.
2. Se actualiza automáticamente al ser un Excel en la Web.
3. Guarda las 20 preguntas de un Test antes de comenzar el siguiente Test.
4. Todo el mundo verá la versión más actualizada del Test.
5. Todos los usuarios harán el Test en las mismas condiciones.
6. Se puede hacer el Test en la nube, incluso sin tener Excel instalado.
7. Se pueden hacer comentarios al Test en la nube.
8. Puedo actualizar el Test cuando quiera para añadir más preguntas.
9. Puedo modificar su comportamiento, mejorar su uso y/o corregir errores.
10. Puedo proteger mucho mejor mis derechos de autor, para que no se pueda copiar mi idea de este Test de Excel.

Vídeo del Test de Excel

En este vídeo explico cómo he hecho el Test de Excel:

Puedes comentarme más abajo las dudas que tengas con alguna respuesta o qué otras preguntas te gustaría que se hicieran en una nueva versión de este Test de Excel...

¿Cómo te ha salido el Test?

¿Crees que ya dominas Excel?

Si has contestado que sí...

¡Ese es el peor error que cometen los principiantes de cualquier nivel de Excel!

Traducción de idiomas con cálculo iterativo

El cálculo iterativo se usa muy poco, principalmente porque no se conoce, y cuando se conoce ¡es difícil usarlo!

Si ya es difícil saber qué fórmulas dependen de qué celdas, más difícil es saber cómo se calculan las fórmulas con referencias circulares, dependientes de la misma celda donde está la fórmula o de vete tú a saber de qué otras celdas dependen.

Para saber cómo habilitar el cálculo iterativo lee este artículo:

Cambiar la precisión, la iteración o el cálculo de fórmulas en Excel (microsoft.com)

Lo difícil es controlar las referencias circulares con el cálculo iterativo pero, como ya he hecho otros ejemplos con el cálculo iterativo (enlace aquí), me he puesto el reto de hacer cuestionarios sin usar macros.

Más abajo podrás descargar una plantilla de Excel, como la de la siguiente imagen animada, que es un Cuestionario de Traducción de Idiomas hecho enteramente con cálculo iterativo. ¡Nada de usar macros VBA!

Vídeo del Cuestionario de Idiomas

En el vídeo explico cómo usar el cuestionario de traducción de idiomas:

      

Características del Cuestionario de Idiomas

Al no usar macros, este Cuestionario se puede usar en Excel para escritorio, en Excel Online o en la Web, e incluso en Excel para Android o Mac, cosa que está prohibida para archivos con macros VBA, que ni siquiera están autorizadas en todas las empresas y en entornos de trabajo seguros...

A la derecha se muestra el aspecto del Cuestionario en Excel Online:

Las principales características del Cuestionario son:

1. Está hecho con el cálculo iterativo de referencias circulares.
2. No contiene macros VBA ni Office Script.
3. Recalcula todo cambiando una única celda MiTraducción: =Traducir!$B$11
4. Pregunta en un idioma seleccionable y hay que responder la traducción en el idioma elegido.
5. Avisa si la traducción es correcta o errónea.
6. Informa del tiempo total de todas las traducciones.
7. Informa del tiempo parcial de una traducción, contabilizando únicamente la suma de tiempos parciales de contestación de las respuestas.
8. Guarda los resultados de la traducciones en la hoja de 'Resultados'.
9. Indica el número de palabras acertadas del total de palabras a traducir.
10. Marca los puntos conseguidos como un factor entre las palabras acertadas y las totales.
11. Se puede configurar el idioma a traducir y el traducido.
12. Se puede configurar el número de traducciones de cada test del cuestionario.
13. Tabla de Idiomas con 9 idiomas precargados: Español; English; Français; Deutsche; Italiano; Português; Català; Galego; Euskara.
14. Se pueden añadir o quitar idiomas.
15. 
    Se pueden modificar, añadir o borrar las palabras y frases a traducir.
16. Funciona en Excel para Android y en Excel para Mac.
17. Funciona en Excel Online o Excel para la Web.

A la derecha se muestra el aspecto del Cuestionario en Excel para Android:

Si quieres probarlo, ¿por qué no lo descargas?

Descarga del Cuestionario de Idiomas

El archivo sin macros y con fórmulas recalculadas iterativamente se puede descargar desde cualquiera de estos enlaces:

• Microsoft OneDrive: Cuestionario Traducción Idiomas - PW4.xlsx 
• Sites Google Drive: Cuestionario Traducción Idiomas - PW4.xlsx

Abrir el archivo y habilitar la edición para analizar las fórmulas.

Cómo se hizo el Cuestionario de Idiomas

Para que funcione el Cuestionario de Idiomas he tenido que configurar el cálculo iterativo mediante estos pasos: 

1. Hacer clic en la pestaña Archivo, seleccionar Opciones y luego hacer clic en la categoría Fórmulas.
2. En la sección Opciones de cálculo, activar la casilla Habilitar cálculo iterativo.
3. Para definir la cantidad de veces que Excel actualizará los cálculos, escribir el número de iteraciones en el cuadro Iteraciones máximas.
4. Para definir el número máximo de cambio que se aceptará entre los resultados de los nuevos cálculos, escribir la cantidad en el cuadro Cambio máximo.

No modifiques estos valores en las Opciones de cálculo de las fórmulas:

Por lo que debe estar habilitado el cálculo iterativo, con 1 iteración y un único cambio, con lo que cada iteración sólo ocurrirá una vez.

¿Cuándo se produce el cálculo iterativo?

En cualquier momento que se recalcule una fórmula

¿Cómo se controla el momento del cálculo iterativo?

Haciendo que sólo cambie una única celda en la hoja principal en la que se van a traducir los idiomas.

¿Dónde se hace el cálculo iterativo?

En la hoja 'Traducir' todas las celdas están bloqueadas excepto la celda B11, en la que el usuario debe escribir la traducción de un idioma al otro.

La modificación de la celda B11 desencadena el recálculo de todas las fórmulas dependientes, en especial de las fórmulas con referencias circulares que se ejecutarán iterativamente una sola vez.

La hoja 'Configuración' contiene dos celdas con referencias circulares que son las que controlan el recálculo iterativo:

- B10 con el Contador.

- B11 con la fila traducida.

La hoja 'Traducir' está protegida sin contraseña, activando únicamente la casilla: Seleccionar celdas desbloqueadas.

Para que no cambie el foco a otra celda de esta hoja, no se debe activar la casilla: Seleccionar celdas bloqueadas.

Como todas las celdas de la hoja 'Traducir' están bloqueadas, para realizar un cambio el usuario debe escribir una traducción en la celda B11 y presionar la tecla Retorno (Enter en inglés), con lo que se muestra la traducción correcta, el resultado del test y los tiempos totales y parciales hasta el momento.

Para borrar el texto en B11, presionar la tecla Supr (Delete en inglés), con lo que pasa a mostrar la siguiente palabra o frase a traducir.

¿Cuáles son las fórmulas de la hoja 'Traducir'?

• Celda B2 informando del contador y del total a traducir:

="TRADUCCIONES: " & MiContador & " de " & MisTraducciones

• Celda B5 - Traducir, con el texto en el idioma a traducir:

=INDICE(TablaIdiomas;MiFila;COINCIDIR(IdiomaTraducir;MisIdiomas;0))

• Celda B8 - Traducción, con el texto en el idioma traducido:

=SI(MiTraducción<>"";INDICE(TablaIdiomas;MiFila;COINCIDIR(IdiomaTraducido;MisIdiomas;0));"")

• Celda C11, con el test de la traducción, siendo correcta o incorrecta:

=SI(Traducción="";"";--IGUAL(Traducción;MiTraducción))

¿Cómo activar hipervínculos en hojas protegidas?

Debido a que la hoja 'Traducir' está protegida sin activar la casilla: Seleccionar celdas bloqueadas, los hipervínculos no funcionan en las celdas bloqueadas, por lo que ha hecho falta insertar formas rectangulares con hipervínculos, encima de las siguientes celdas, como una solución alternativa (workaround en inglés):

• Celda B2, para ir a cambiar el Nº de traducciones: 'Configuración'!B8.
• Celda B4, para ir a cambiar el Idioma a traducir: 'Configuración'!B4.
• Celda B7, para ir a cambiar el Idioma traducido: 'Configuración'!B6.
• Celda B10, para ir a la hoja de 'Idiomas'.
• Celda C2, para ir a la hoja de 'Resultados'.
• Celda B14, para ir al Blog: https://pedrowave.blogspot.com
• Celda C14, para ir al Copyright: https://creativecommons.org/licenses/by-nc-sa/3.0/deed.es
• Imagen Copyright, para ir al Copyright anterior.

¿Cómo comprobar los resultados del cuestionario?

Cuando se han traducido el Nº de traducciones programadas, se marcan en fondo de color naranja las celdas B11 y C2, para avisar que se puede  Ir a Resultados , para comprobar los resultados del cuestionario antes de suprimir la última respuesta.

Haciendo clic en la celda C2 se va a la hoja 'Resultados', que avisa con este mensaje:

"Recalcular resultados con la tecla F9"

Presionando la tecla F9 se recalcula el resumen de resultados, informando del número de traducciones acertadas del total, del tiempo parcial de esas traducciones, del número de palabras acertadas del total de palabras y del número de puntos referidos al porcentaje de palabras acertadas sobre el total de palabras a traducir.

¿Cómo interpretar los resultados del cuestionario?

• Columna A: con el número de respuesta.
• Columna B: con la fila traducida de la hoja 'Idiomas'.
• Columna C: con la traducción correcta.
• Columna D: con la traducción del usuario.
• Columna E: con el resultado del test con un símbolo de correcto o incorrecto.
• Columna F: con el tiempo parcial de cada respuesta, sin incluir tiempos de cambio de pregunta.
• Columna G: con el número de palabras de cada una de las traducciones correctas.
• Columna H: con el número de palabras acertadas en cada traducción.

¿Cómo configurar el cuestionario?

La hoja 'Configuración' permite configurar el cuestionario:

• Celda B4 con el Idioma a traducir entre 9 preconfigurados.
• Celda B6 con el Idioma traducido.
• Celda B8 con el Nº de traducciones de 5 a 30.

¿Cómo se hace el recálculo iterativo del cuestionario?

Con estas celdas auxiliares de la hoja 'Configuración':

• Celda B10 - MiContador: con el Contador de traducciones, con una fórmula con referencias circulares a esa misma celda.

=SI(Y(MiContador>=@MisTraducciones;MiTraducción="");0;MiContador+SI(O(MiTraducción="";Traducción<>"");0;1))

• Celda B11 - MiFila: con el número de fila traducida, calculada aleatoriamente con una fórmula con referencias circulares a esa misma celda.

=SI(O(MiFila=0;Y(MiTraducción="";Traducción=""));ALEATORIO.ENTRE(1;MisFilas);MiFila)

• Celda B12 - MisFilas: con el número de filas totales a traducir.

=CONTARA(Idiomas!$A:$A)-1

• Celda B14 - MiAhora: con la hora actual.

=AHORA()

• Celda B15 - MiHoraTotal: con la hora inicial del cuestionario, con una fórmula con referencias circulares a esa misma celda.

=SI(MiContador=0;MiAhora;MiHoraTotal)

• Celda B16 - MiTiempoTotal: con el tiempo total del cuestionario.

=MiAhora-MiHoraTotal

• Celda B17 - MiHoraParcial: con la hora de la pregunta actual del cuestionario, con una fórmula con referencias circulares a esa misma celda.

=SI(MiTraducción="";MiAhora;MiHoraParcial)

• Celda B18 - MiTiempoParcial: con el tiempo parcial de la pregunta actual del cuestionario.

=MiAhora-MiHoraParcial

¿Dónde están las traducciones?

En la hoja 'Idiomas' está la tabla con los 9 idiomas predefinidos, con las palabras y frases a traducir.

Se pueden añadir o quitar los idiomas de cada columna, y se puede modificar cualquier traducción, quitarla o añadir nuevas palabras o frases para traducir.

¿Has usado alguna vez el cálculo iterativo?

Con este ejemplo ya puedes tratar de usar el cálculo iterativo en tus próximas fórmulas de Excel.

¿Se te ocurren más usos del cálculo iterativo?

En un próximo artículo trataré de hacer un cuestionario de preguntas y respuestas que te gustará.

5º truco: Separar nombres y apellidos con fórmulas

Para separar nombres y apellidos compuestos he publicado 5 trucos, 3 de ellos diseñados por ExcelForo de Ismael Romero, Microsoft MVP Excel, y 2 de ellos son propiamente míos. En este artículo he publicado el 5º truco:

Caja negra del Relleno rápido simulado con fórmulas | Pedro Wave for Excel Guys

En él se ve cómo sale el conejo de la chistera, pero no se ve qué pasa dentro de la chistera del mago, pues las fórmulas que lo hacen posible están ocultas.

Y eso es lo que voy a explicar ahora: cómo ha conseguido el mago realizar la magia del 5º truco para conseguir separar nombres y apellidos compuestos.

Y como no me gusta hacer esperar, en esta imagen descubro todas las fórmulas que hay dentro de la chistera para realizar el 5º truco:

Descargar el 5º truco con fórmulas

El archivo con el 5º truco, para separar nombres y apellidos compuestos, con fórmulas comunes y corrientes que funcionan en todas las versiones de Excel, se puede descargar desde cualquiera de estos enlaces:

• Microsoft OneDrive: Separar nombres y apellidos compuestos - PW1.xlsx 
• Sites Google Drive: Separar nombres y apellidos compuestos - PW1.xlsx

Abrir el archivo y habilitar la edición para analizar las fórmulas.

Sustituciones del 5º truco

Con este truco se pueden separar nombres y apellidos como los de este ejemplo:

• María de Lourdes Espinoza de los Monteros
• Nombre: María de Lourdes
• Apellido1: Espinoza
• Apellido2: de los Monteros

El 5º truco se basa en esta tabla de la hoja 'Sustituciones':

En esta tabla están los 24 tipos de sustituciones que se harán en la hoja 'Separar nombres'.

La tabla de la hoja 'Sustituciones' contiene las siguientes columnas:

• A) Sustituciones: en esta columna se editan los textos a sustituir.
• B) 1º Sustituir: se añade un espacio a la derecha e izquierda con la fórmula: =" "&A2&" "
• C) 1º Por: se añade un espacio a la izquierda del valor de la columna D, con la fórmula: =" "&D2
• D) 2º Sustituir: se quitan los espacios y se añaden guiones a derecha e izquierda con la fórmula: ="-"&SUSTITUIR(A2;" ";"")&"-"
• E) 2º Por: se añade un espacio a la derecha con la fórmula: =A2&" "

Respecto a la anterior versión he incluido dos sustituciones más: "st" y "St", con lo que se pueden separar apellidos franceses, además de apellidos españoles.

Fórmulas del 5º truco

En la hoja 'Separar nombres' he incluido varias columnas auxiliares para separar los nombres y apellidos compuestos en: Nombre, Apellido1 y Apellido2.

Han sido probados 69 ejemplos de nombres compuestos en la columna A, que es el mismo escenario de casos probados para los otros 4 trucos:

Han hecho falta 3 columnas auxiliares B, C y D para conseguir la separación de los nombres y apellidos compuestos en 3 columnas: E, F y G.

El 5º truco se consigue con las siguientes fórmulas en cada columna:

• A) Nombres compuestos: en esta columna se editan los nombres y apellidos a separar.

• B) Nombres compuestos abreviados: se hace la primera sustitución de los datos de la columna A, sustituyendo en un bucle ordenado los 24 valores posibles de la tabla de la hoja 'Sustituciones', con la fórmula:

=SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(A2; Sustituciones!$B$2;Sustituciones!$C$2); Sustituciones!$B$3;Sustituciones!$C$3); Sustituciones!$B$4;Sustituciones!$C$4); Sustituciones!$B$5;Sustituciones!$C$5); Sustituciones!$B$6;Sustituciones!$C$6); Sustituciones!$B$7;Sustituciones!$C$7); Sustituciones!$B$8;Sustituciones!$C$8); Sustituciones!$B$9;Sustituciones!$C$9); Sustituciones!$B$10;Sustituciones!$C$10); Sustituciones!$B$11;Sustituciones!$C$11); Sustituciones!$B$12;Sustituciones!$C$12); Sustituciones!$B$13;Sustituciones!$C$13); Sustituciones!$B$14;Sustituciones!$C$14); Sustituciones!$B$15;Sustituciones!$C$15); Sustituciones!$B$16;Sustituciones!$C$16); Sustituciones!$B$17;Sustituciones!$C$17); Sustituciones!$B$18;Sustituciones!$C$18); Sustituciones!$B$19;Sustituciones!$C$19); Sustituciones!$B$20;Sustituciones!$C$20); Sustituciones!$B$21;Sustituciones!$C$21); Sustituciones!$B$22;Sustituciones!$C$22); Sustituciones!$B$23;Sustituciones!$C$23); Sustituciones!$B$24;Sustituciones!$C$24); Sustituciones!$B$25;Sustituciones!$C$25)

• C) Nombres compuestos separados por comas: se hace la segunda sustitución de los datos de la columna B, sustituyendo en primer lugar los espacios por comas, y luego en un bucle ordenado los 24 valores posibles de la tabla de la hoja 'Sustituciones', con la fórmula:

=SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(B2;" ";","); Sustituciones!$D$2;Sustituciones!$E$2); Sustituciones!$D$3;Sustituciones!$E$3); Sustituciones!$D$4;Sustituciones!$E$4); Sustituciones!$D$5;Sustituciones!$E$5); Sustituciones!$D$6;Sustituciones!$E$6); Sustituciones!$D$7;Sustituciones!$E$7); Sustituciones!$D$8;Sustituciones!$E$8); Sustituciones!$D$9;Sustituciones!$E$9); Sustituciones!$D$10;Sustituciones!$E$10); Sustituciones!$D$11;Sustituciones!$E$11); Sustituciones!$D$12;Sustituciones!$E$12); Sustituciones!$D$13;Sustituciones!$E$13); Sustituciones!$D$14;Sustituciones!$E$14); Sustituciones!$D$15;Sustituciones!$E$15); Sustituciones!$D$16;Sustituciones!$E$16); Sustituciones!$D$17;Sustituciones!$E$17); Sustituciones!$D$18;Sustituciones!$E$18); Sustituciones!$D$19;Sustituciones!$E$19); Sustituciones!$D$20;Sustituciones!$E$20); Sustituciones!$D$21;Sustituciones!$E$21); Sustituciones!$D$22;Sustituciones!$E$22); Sustituciones!$D$23;Sustituciones!$E$23); Sustituciones!$D$24;Sustituciones!$E$24); Sustituciones!$D$25;Sustituciones!$E$25)

• D) Nombres y Apellidos separados por comas: los valores de la columna C se separan con comas por un lado el Nombre compuesto y por otro el Apellido1 del Apellido2, con la fórmula:

=SI.ERROR(ELEGIR(LARGO(C2)-LARGO(SUSTITUIR(C2;",";""));C2;C2;SUSTITUIR(C2;",";" ";1);SUSTITUIR(SUSTITUIR(C2;",";" ";1);",";" ";1));C2)

• E) Nombre: se obtiene el nombre partiendo de la columna D, con la fórmula:

=SI.ERROR(IZQUIERDA(D2;ENCONTRAR(",";D2)-1);"")

• F) Apellido1: se obtiene el primer apellido con la fórmula:

=SI.ERROR(IZQUIERDA(SUSTITUIR(D2;E2&",";"");ENCONTRAR(",";SUSTITUIR(D2;E2&",";""))-1);"")

• G) Apellido2: se obtiene el segundo apellido con la fórmula:

=SUSTITUIR(D2;E2&","&F2&",";"")

Con otra tabla adicional se comprueba si ha sido correcta la separación de los nombres compuestos, gracias a estas 2 columnas:

• I) Nombre completo: se concatenan los nombres y apellidos con la fórmula:

=ESPACIOS(E2&" "&F2&" "&G2)  

• J) Chequeo: se chequean los nombres compuestos de las columnas A e I, con la fórmula:

=IGUAL(A2;I2)

¡Truco desvelado!

Por favor, comenta si encuentras algún caso de nombres y apellidos compuestos que no se puedan separar.

Ahora solo falta que alguien meta en la chistera una megafórmula que haga lo mismo.

Enlaces a todos los artículos sobre el Relleno Rápido:

1. Separar nombres compuestos con Relleno rápido
2. Cómo separar nombres y apellidos con Relleno rápido
3. Separar nombres y apellidos con 3 trucos + Relleno rápido
4. Caja negra del Relleno rápido simulado con fórmulas
5. 5º truco: Separar nombres y apellidos con fórmulas

Caja negra del Relleno rápido simulado con fórmulas

Este va a ser el quinto y penúltimo (a la espera de que una actualización de la IA del Relleno rápido lo haga posible) truco para separar nombres y apellidos compuestos, que es la continuación de este otro artículo con 4 trucos, siendo uno el de relleno:

Separar nombres y apellidos con 3 trucos + Relleno rápido

Como un amigo mío dice: una solución debe ser clara de entender y sencilla de aprender, para que sea comprendida y asimilada por la inmensa mayoría de la gente.

Y eso me propongo, dejar de lado los 4 trucos mostrados en el artículo anterior:

1. Las macros VBA, que únicamente las comprenden los usuarios expertos en Excel.
2. Power Query, que sigue sin ser usado por la mayoría de usuarios de Excel.
3. Las nuevas fórmulas y funciones avanzadas, como LET y LAMBDA, que únicamente existen en las últimas versiones de Excel.
4. La Inteligencia Artificial (IA) de Excel, como la incorporada en el Relleno rápido, que es como una gran caja negra de la que no se sabe nada de su funcionamiento interno, además de ser un método estático que debe ser ejecutado cada vez que se requiera, dándole varios ejemplos diferentes que sirvan de semilla a la IA, hasta conseguir el resultado deseado.

Os presento el 5º truco que únicamente se basará en fórmulas y funciones corrientes, de las que existen en cualquier versión de Excel, para que sea fácil de entender por todo el mundo cómo separar nombres y apellidos compuestos, como en el ejemplo de la imagen:

5º truco para separar nombres y apellidos

Este truco de magia lo he sacado de la chistera con la varita mágica de unas fórmulas con funciones comunes de Excel que he dejado ocultas, para mostrar sólo la patita del conejo, como hace la IA - Inteligencia Artificial de Excel con el Relleno rápido, ocultando los cálculos dentro de una caja negra, que es la chistera del mago, y mostrando sólo el resultado, que es el conejo sacado de la chistera.

Ojalá algún día este 5º truco se pueda hacer totalmente con la funcionalidad del Relleno rápido, y no haga falta hacerlo con fórmulas o con los 4 trucos anteriores.

En esta plantilla en la nube de OneDrive puedes probar lo que digo y ver el 5º truco en acción:

En esta plantilla puedes editar los nombres compuestos de la columna A, obteniendo automáticamente los nombres y apellidos separados en las columnas E, F y G, sin saber cómo se ha conseguido hacer la separación, pues las columnas B, C y D están ocultas y también están ocultas sus fórmulas. Es lo que hace una caja negra: con datos de entrada obtener un resultado de salida, sin dar pistas de cómo lo hace internamente.

Para chequear si son correctos los resultados obtenidos por la caja negra, con las fórmulas ocultas, he añadido las columnas I y J. En la columna I se concatenan los nombres y apellidos. En la columna J se comprueba si los valores de las columnas A e I son iguales, comparando las entradas con las salidas de la caja negra.

Carencias del Relleno rápido

Usando el Relleno rápido (Flash Fill en inglés) he detectado algunas carencias que quiero hacer públicas para que, si llegan a oídos del mago que está al mando de la Inteligencia Artificial de Excel, pueda mejorar esta extraordinaria funcionalidad en próximas versiones de Excel.

Como tengo contacto en LinkedIn con José Cambronero, que forma parte del equipo investigador de esta funcionalidad de Excel, sirva este artículo para comunicarle algunas carencias detectadas al usar el Relleno rápido:

• 1º) Mejorar la separación de nombres y apellidos compuestos, sobre todo si son nombres en español, lo que ya deben saber por mis anteriores artículos. Por ejemplo:

• 2º) Ayudar a entender lo que hace internamente la caja negra del Relleno rápido, cuando rellena automáticamente una columna. El mensaje de advertencia, que indica que no se ha observado ningún patrón para rellenar valores, es demasiado críptico. Sería mejor indicar cuál de los ejemplos introducidos ha causado la falta de un patrón de relleno, poniendo la celda activa en ese ejemplo para que sea fácil de detectar. En el ejemplo de la imagen debería avisar que ha sido el ejemplo de la celda B3 el que ha causado el mensaje de aviso pues considera que, en lugar de haber escrito Carolina, el ejemplo debería haber sido: Carolina Darias, aunque no sea lo que pretendíamos pues Darias es su primer apellido.

• 3º) En el menú desplegado informar qué patrón ha usado el Relleno rápido o sino, al menos, incluir otra entrada más para: Seleccionar las xx celdas de ejemplo

• 4º) Posibilidad de enviar a Microsoft información si no encuentra un patrón, con los datos que han impedido el relleno automático, para su posterior análisis por el equipo de investigación del Relleno rápido. Un extra sería la posibilidad de añadir algún comentario por parte del usuario antes de enviarlo. Esos ejemplos podrían servir para mejorar el algoritmo de cálculo interno del Relleno rápido. Es lo que los datos de información geográfica permiten hacer: después de Mostrar la tarjeta, abajo a la derecha aparece una bandera que, al pulsarla, abre una ventana para informar de Problemas con datos del informe. ¿Por qué no podemos informar de la misma manera de Problemas con datos del Relleno rápido?

• 5º) Conseguir que el Relleno rápido sea dinámico no es posible con la tecnología actual. Ahora mismo es estático y hay que ejecutarlo de nuevo cuando cambian lo ejemplos de cálculo de los patrones o los datos de partida con los que se hará el Relleno rápido. Lo asumo como una carencia debida a la Inteligencia Artificial de Excel, ¡que no gestiona bien los cambios! ;-)

• 6º) IMPORTANTE: La versión Web de Excel no recalcula automáticamente las fórmulas dependientes de las celdas rellenadas automáticamente por el Relleno rápido.

Vamos a reproducir con un ejemplo esta importante carencia del Relleno rápido en la versión de Excel en la Web.

En la columna D se escriben los ejemplos que ayudarán al Relleno rápido a encontrar un patrón de relleno. En la columna F hay una fórmula para sustituir preposiciones y artículos de nombres y apellidos compuestos, fórmula dependiente de los datos de la columna D.

En la celda D2 se ha escrito el primer ejemplo y en F2 aparece automáticamente el resultado de la fórmula con las sustituciones.

En G2, H2 e I2 se han escrito los ejemplos para lanzar más tarde 3 veces el Relleno rápido, pero estas columnas no aportan nada a la falta de recálculo de la columna F.

Al escribir el segundo y tercer ejemplo en las celdas D4 y D6, se recalculan automáticamente las fórmulas en F4 y F6, ¡como debe ser!:

Seleccionando una celda cualquiera de la columna D y ejecutando desde la cinta de opciones: Datos > Relleno rápido, se rellenan automáticamente las celdas de la columna D, como se esperaba:

ATENCIÓN:

Si observas bien la imagen anterior, las fórmulas de la columna F, dependientes de la columna D, no han sido recalculadas automáticamente, como lo fueron las 3 celdas dependientes cuando se escribieron los 3 casos de ejemplo.

AVISO: Este efecto indeseado no se reproduce en la versión de escritorio de Excel, únicamente lo he detectado en la versión Web de Excel, con las Opciones para el cálculo de fórmulas en modo Automático. Tampoco se recalculan las fórmulas de la columna F presionando los botones: Calcular libro o Calcular hoja

WORKAROUND:

¿Cuál es la solución alternativa (workaround en inglés) a este problema de recálculo?

Pues seleccionar la celda F2, colocarse en la esquina inferior derecha de esa celda, y hacer doble clic en el símbolo de cruceta para arrastrar la fórmula de F2 hacia abajo, con lo que se recalculan correctamente las fórmulas de la columna F.

Ahora sí se han recalculado las fórmulas de las celdas de la columna F, con fórmulas dependientes de la columna D, que había sido rellenada automáticamente con el Relleno rápido.

La cuestión es por qué ha hecho falta hacer el recálculo de las fórmulas con un workaround cuando deberían haber sido recalculadas automáticamente.

Descarga este archivo de ejemplo para poder reproducir el problema de recálculo comentado:

• Microsoft OneDrive: Relleno Rápido PW3.xlsx 

Una vez descargado el archivo lo subes a tu nube en Microsoft OneDrive y lo abres en Excel en la Web, para reproducir el problema de recálculo con los pasos explicados más arriba.

Próximos pasos con el Relleno rápido

Si detecto más carencias o se me ocurren más mejoras las añadiré a partir de aquí en este mismo artículo.

Si se te ocurren otras carencias o mejoras del Relleno rápido, que te gustaría que fueran incluidas en una versión futura de Excel, te animo a hacer un comentario más abajo, y puede que incluso le interese conocerlas al inventor de esta excelente funcionalidad, que parece pura magia: Sumit Gulwani

Un buen artículo en inglés sobre el Relleno rápido:

Everything You Need To Know About Flash Fill In Microsoft Excel

En el próximo artículo enseñaré las fórmulas del 5º truco, para separar los nombres y apellidos compuestos, y podrás descargar un archivo de ejemplo.

Enlaces a todos los artículos sobre el Relleno Rápido:

1. Separar nombres compuestos con Relleno rápido
2. Cómo separar nombres y apellidos con Relleno rápido
3. Separar nombres y apellidos con 3 trucos + Relleno rápido
4. Caja negra del Relleno rápido simulado con fórmulas
5. 5º truco: Separar nombres y apellidos con fórmulas

Separar nombres y apellidos con 3 trucos + Relleno rápido

En el artículo anterior usé el Relleno rápido para separar nombres y apellidos compuestos, puedes leerlo en este enlace:

Cómo separar nombres y apellidos con Relleno rápido

Pero faltaba separar algunos tipos de nombres y apellidos que no estaban contemplados en dicho artículo, lo que se conseguirá con el archivo que adjunto más abajo, y se puede ver un anticipo en esta imagen animada:

Los nombres y apellidos compuestos, que no estaban contemplados en la versión anterior, contienen preposiciones, artículos y contracciones: De, de, de los, del, etc., como en estos 3 ejemplos:

Por lo que no hacia correctamente la separación con estos tipos de nombres compuestos.

3 trucos de Ismael Romero

En el blog ExcelForo de Ismael Romero, Microsoft MVP Excel, explica 3 trucos para separar un nombre compuesto o no, devolviéndolo como nombre y dos apellidos, mediante macros VBA, Power Query o solo con fórmulas recientes de Excel, que recomiendo leer encarecidamente:

• VBA: Descomponer Nombres y Apellidos compuestos
• Power Query: Separar Nombres y Apellidos
  
• Separar Nombres y Apellidos solo con fórmulas
  

Por suerte, existen una serie limitada de combinaciones de palabras que se emplean en los nombres y apellidos compuestos (ordenados en sentido ascendente): "de", "del", "el", "la", "las", "los", "san", "y". En estas combinaciones de palabras se basan las separaciones propuestas por Ismael Romero.

Vídeo de Sergio Alejandro Campos

En el blog EXCELeINFO de Sergio Alejandro Campos, Microsoft MVP Excel, se publicó este artículo:

• 3 trucos para separar nombres y apellidos compuestos en Excel por Ismael Romero, MVP

En el que se puede descargar un archivo de ejemplo con los 3 trucos de Ismael Romero y se puede ver este vídeo explicándolos:

Análisis de los 3 trucos de Ismael Romero

Analizando los 3 trucos de Ismael Romero me planteé el reto de hacer lo mismo con la característica de Datos > Relleno rápido, sabiendo que iba a ser un reto difícil de resolver.

Uno de los trucos se resuelve con una función matricial definida por el usuario (UDF - User Defined Function) por lo que, para entender este truco, hay que ser un usuario de Excel experimentado con las macros VBA. Enlace aquí:

• VBA: Descomponer Nombres y Apellidos compuestos

Otro truco se resuelve con Power Query, herramienta integrada en versiones recientes de Excel, por lo que, para entender este truco, hay que conocer Power Query, cosa que no está al alcance de todos los usuarios de Excel. Enlace aquí:

• Power Query: Separar Nombres y Apellidos

El tercer truco usa las funciones más recientes incluidas en las últimas versiones de Excel, como: la función web XMLFILTRO; UNIRCADENAS o las nuevas funciones LET y LAMBDA. Enlace aquí:

• Separar Nombres y Apellidos solo con fórmulas

Ninguno de estos trucos son fáciles de comprender, ni siquiera para un usuario avanzado de Excel, y menos por un usuario que no conozca VBA, Power Query o las nuevas y más recientes funciones de Excel, que solamente están incluidas en las actualizaciones más recientes de las suscripciones de Excel para Microsoft 365. Por ejemplo, en mí suscripción aún no aparece la nueva función LAMBDA.

Nuevo truco para separar con Relleno rápido

Para el nuevo truco que separa nombres y apellidos compuestos uso el Relleno rápido, aunque me he encontrado con limitaciones para conseguirlo usando únicamente el Relleno rápido.

He probado a darle ejemplos a la Inteligencia Artificial del Relleno rápido de apellidos compuestos con la preposición "de", con artículos y contracciones, como este caso: María de Lourdes Espinoza de los Monteros, donde se incluyen: "de" y "de los", pero no he conseguido que el Relleno rápido separe correctamente esos tipos de nombres y apellidos compuestos.

Antes de atacar el problema he leído este artículo para saber:

¿Cómo se escriben los nombres y apellidos compuestos?

Compruebo que es importante mantener las mayúsculas y minúsculas, por lo que tengo que usar funciones sensibles a mayúsculas y minúsculas y crear una tabla que las contenga, en un orden determinado para sustituir antes "de los" que "de", por ejemplo, como se comprende inmediatamente. O sea, el orden de la tabla de sustituciones es relevante.

En la hoja 'Sustituciones' he creado una tabla con las combinaciones siguientes de preposiciones, artículos y contracciones:

Explicación de las 5 columnas de la tabla:

A) Sustituciones: en esta columna se editan los textos a sustituir.
B) 1º Sustituir: se añade un espacio a la derecha e izquierda con la fórmula: =" "&A2&" "
C) 1º Por: se añade un espacio a la izquierda del valor de la columna D, con la fórmula: =" "&D2
D) 2º Sustituir: se quitan los espacios y se añaden guiones a derecha e izquierda con la fórmula: ="-"&SUSTITUIR(A2;" ";"")&"-"
E) 2º Por: se añade un espacio a la derecha con la fórmula: =A2&" "

¿Por qué hacen falta las 5 columnas? ¡Para que sean más simples las 44 sustituciones que hay que hacer!

¿Dónde se hace las 44 sustituciones? En la hoja 'Relleno rápido' ¡22 sustituciones en la columna C y otras 22 en la columna F!

Por ejemplo, en la celda C10 la mega-fórmula de la imagen transforma:

• María de Lourdes Espinoza de los Monteros en
• María -de-Lourdes Espinoza -delos-Monteros

donde " de los " se ha convertido en "-delos-" y " de " se ha convertido en "-de-", en ese orden, sin espacios en blanco y con guiones a derecha e izquierda.

Por ejemplo, en la celda F10 la mega-fórmula de la imagen transforma:

• María -de-Lourdes,Espinoza,-delos-Monteros en
• María de Lourdes,Espinoza,de los Monteros

donde "-de-" se ha convertido en "de " y "-delos-" se ha convertido en "de los ", sin guiones y con espacios. Aquí no importa el orden al estar separadas las abreviaturas por guiones.

Las fórmulas que realizan las sustituciones llaman a la función SUSTITUIR en bucle de 22 repeticiones, desde la fila 2 a la 23 de la hoja 'Sustituciones', siguiendo el orden de la tabla con las 22 sustituciones posibles, aunque siempre se puede editar la columna A e incluir más filas.

La primera sustitución en la columna C es:

SUSTITUIR(A2;Sustituciones!$B$2;Sustituciones!$C$2)

La primera sustitución en la columna F es:

SUSTITUIR(D2;Sustituciones!$D$2;Sustituciones!$E$2)

Ya se ha visto que las otras sustituciones se hacen con bucles anidados de 22 sustituciones en cada fórmula. A continuación explicaré para qué uso el Relleno rápido.

Cómo separar con Relleno rápido

El relleno rápido se debe hacer en 2 pasos:

1. Para obtener en la columna D los nombres y apellidos separados por comas, partiendo de los nombres y apellidos separados por espacios de la columna C. Por ejemplo: Berenice Naranjo González se transforma en Berenice,Naranjo,González
2. Para obtener en las columnas G (Nombre), H (Apellido1) e I (Apellido2) los nombres y apellidos separados, partiendo de los nombres y apellidos de la columna F. Por ejemplo: Berenice,Naranjo,González se transforma en 3 valores separados: Berenice Naranjo González

Para ejecutar los 2 pasos con el Relleno rápido ha sido necesario separar los pasos en 2 tablas separadas:

• TablaRellenoRápidoPaso1: en las columnas C:D
• TablaRellenoRápidoPaso2: en las columnas F:I

El motivo es que el Relleno rápido se hace desde la columna izquierda de esas tablas.

Para que el Relleno rápido sea capaz de rellenar automáticamente los valores de una columna, se deben escribir al menos un par de ejemplos con el resultado que se pretende obtener, y mantener la celda activa en la columna que se vaya a rellenar, presionando: Datos > Relleno rápido

Para el paso 1 han hecho falta escribir 3 ejemplos (en amarillo), con los que se consigue el Relleno rápido del resto de valores de la columna D:

Para el paso 2 sólo hace falta escribir un ejemplo (en amarillo) con lo que, ejecutando 3 veces el Relleno rápido, se consiguen el resto de valores de las columnas G, H e I:

He añadido una tabla más, para chequear el resultado de la separación de los nombres y apellidos, en las columnas K y L: TablaRellenoRápidoChequeo

En la columna K se concatenan los valores separados, con la fórmula: =G2&" "&H2&" "&I2

En la columna L se comprueba si los valores de las columnas A y K son iguales, con la fórmula: =IGUAL(A2;K2)

Se comprueba que todos los valores son iguales, pues su chequeo es VERDADERO, por lo que hemos conseguido separar todos los nombres y apellidos compuestos que se intentaban separar con los 3 trucos de Ismael Romero, como era el objetivo de este artículo.

Aunque no es del todo cierto que la separación con los 3 trucos anteriores sea correcta porque:

Con los trucos: Funciones LET y LAMBDA y VBA y Macros, se marca en naranja este error:

Con el truco:  Lenguaje M con Power Query, se marca en naranja este error:

Cuando hay tres nombres en la parte del nombre, por ejemplo: Juan Carlos Evaristo, únicamente separa los dos primeros nombres: Juan Carlos, y pone incorrectamente como 1er Apellido el tercer nombre: Evaristo, y erróneamente como 2do Apellido: Peña, confundiendo los apellidos pues es: Gutierrez, que desaparece de los apellidos.

Con el 4º truco que he presentado en este artículo, gracias al Relleno rápido, la separación de nombres y apellidos es correcta para este caso:

Por lo que habría que depurar los otros 3 trucos de Ismael Romero para que contemplen ese caso, con hasta 3 nombres en un mismo nombre... El nombre de mi madre era Luisa Julia Joaquina, por lo que no es tan raro tener 3 nombres...

Descargar el 4º truco con Relleno rápido

El archivo con los 4 trucos se puede descargar desde cualquiera de estos enlaces:

• Microsoft OneDrive: Separar nombres y apellidos compuestos - ExcelForo - EXCELeINFO - PW3.xlsm 
• Sites Google Drive: Separar nombres y apellidos compuestos - ExcelForo - EXCELeINFO - PW3.xlsm

Habilita la edición y las macros al abrir el archivo.

El archivo original con los 3 primeros trucos se puede descargar desde este artículo:

• 3 trucos para separar nombres y apellidos compuestos en Excel por Ismael Romero, MVP

A este archivo es al que le he añadido el 4º truco con Relleno rápido, y lo comparto después de solicitar permiso al autor de los 3 primeros trucos, Ismael Romero, que me contestó muy amablemente lo siguiente:

Hola Pedro!

por supuesto que sí... muchas gracias por 'solicitarlo', a veces se echa de menos ;-)

Será interesante ver el comportamiento del relleno rápido con los casos de nombres 'más complejos'.

Espero esos artículos con interés!!

Un cordial saludo (y buen fin de semana)

Ismael

También solicité permiso al autor del vídeo que se ve al principio de este artículo, Sergio Alejandro Campos, que contestó lo siguiente:

Hola Pedro, Adelante, solo haz referencia de dónde lo tomas, para beneficiarnos todos.

Descarga el 4º truco con Relleno rápido desde la nube

Si no quieres descargar el archivo anterior con los 4 trucos, puedes descargar únicamente el 4º truco, para separar nombres y apellidos compuestos con Relleno rápido, desde la nube de Microsoft:

En el último artículo de esta serie explico el 5º truco: Cómo separar nombres y apellidos compuestos únicamente con fórmulas y funciones corrientes, de las que sirven en cualquier versión de Excel. ¡Nada de usar las nuevas funciones LET y LAMBDA!

Enlaces a todos los artículos sobre el Relleno Rápido:

1. Separar nombres compuestos con Relleno rápido
2. Cómo separar nombres y apellidos con Relleno rápido
3. Separar nombres y apellidos con 3 trucos + Relleno rápido
4. Caja negra del Relleno rápido simulado con fórmulas
5. 5º truco: Separar nombres y apellidos con fórmulas