3 trucos para separar nombres y apellidos compuestos en Excel por Ismael Romero, MVP
En este video tutorial 3 maneras para separar nombres y apellidos compuestos en Excel. Éstos 3 trucos son autoría de Ismael Romero, MVP de Excel. Ismael me dio autorización de compartir su trabajo.
Aquí pueden consultar la página de Excel Foro.
En este mismo Blog, ya habíamos visto una manera de separar nombres en Excel.
3 maneras para separar nombres y apellidos en Excel
Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.
1. Separar nombres con fórmulas y funciones (LET y LAMBDA)
Con esta mega fórmulas vamos a poder separar nombres y apellidos compuestos. Se usa principalmente la función LET, BUSCARV y XMLFILTRO, lo cual la hace una fórmula muy potente.
El primer requisito para que esta fórmula funcione con apellidos compuestos, es que debes tener una tabla llamada TblReemp, la cual contenga los separadores que quieras usar para detectar apellidos compuestos.
Figura 1. Tabla de separadores para nombres compuestos en Excel.
Con autorización de Ismael, te comparto la fórmula:
=SI.ERROR(LET(Paso0,SUSTITUIR(NOMPROPIO(A2),” “,”| “),
Paso1,UNIRCADENAS(” “,0,
SI.ERROR(BUSCARV(XMLFILTRO(“<ini><fila><dato>”&SUSTITUIR(Paso0,” “,”</dato><dato>”)&”</dato></fila></ini>”,”//ini/fila/dato”),TblReemp,2,0),
XMLFILTRO(“<ini><fila><dato>”&SUSTITUIR(Paso0,” “,”</dato><dato>”)&”</dato></fila></ini>”,”//ini/fila/dato”))),
Partes,LARGO(Paso1)-LARGO(SUSTITUIR(Paso1,”|”,””))+1,
part1,TRANSPONER(XMLFILTRO(“<ini><fila><dato>”&SUSTITUIR(Paso1,”|”,”</dato><dato>”)&”</dato></fila></ini>”,”//ini/fila/dato[1]”)),
part2,TRANSPONER(XMLFILTRO(“<ini><fila><dato>”&SUSTITUIR(Paso1,”|”,”</dato><dato>”)&”</dato></fila></ini>”,”//ini/fila/dato[2]”)),
part3,TRANSPONER(XMLFILTRO(“<ini><fila><dato>”&SUSTITUIR(Paso1,”|”,”</dato><dato>”)&”</dato></fila></ini>”,”//ini/fila/dato[3]”)),
part4,TRANSPONER(XMLFILTRO(“<ini><fila><dato>”&SUSTITUIR(Paso1,”|”,”</dato><dato>”)&”</dato></fila></ini>”,”//ini/fila/dato[4]”)),
SI(Partes>3,
TRANSPONER(XMLFILTRO(“<ini><fila><dato>”&UNIRCADENAS(“</dato><dato>”,FALSO,part1&” “&part2,part3,SI.ERROR(part4,””))&”</dato></fila></ini>”,”//ini/fila/dato”)),
TRANSPONER(XMLFILTRO(“<ini><fila><dato>”&UNIRCADENAS(“</dato><dato>”,FALSO,part1,SI.ERROR(part2,””),SI.ERROR(part3,””))&”</dato></fila></ini>”,”//ini/fila/dato”)))),””)
Mira el video si quieres usar esta fórmula en combinación con la función LAMBDA.
2. Usando VBA y macros (UDF) en Excel
Ahora te comparto una función UDF escrita con VBA que hará lo mismo que la fórmula anterior, pero usando nuestras queridas macros. La función UDF se llama NombreApellidos y solo recibe el parámetro del nombre completo.
Aquí el código VBA de la función UDF
'''''''''''''''''''''''''''''''''''''''''''' 'Por Ismael Romero, MVP, excelforo.com '''''''''''''''''''''''''''''''''''''''''''' Function NombreApellidos(NombreCompuesto As String) Dim aApellido() As String, N_Apellido As String 'separamos las partes de los apellidos aApellido = Split(NombreCompuesto, " ") 'recorremos cada palabra... For elto = 0 To UBound(aApellido) 'homogeneizamos todo en minúscula Select Case LCase(aApellido(elto)) Case "de", "del", "el", "la", "las", "los", "san", "y" N_Apellido = N_Apellido & aApellido(elto) & " " Case Else 'si es la última parte del apellido If elto = UBound(aApellido) Then 'no añadimos ningún separador al final. N_Apellido = N_Apellido & aApellido(elto) Else 'en caso contrario añadimos una barra vertical | N_Apellido = N_Apellido & aApellido(elto) & "|" End If End Select Next elto 'contamos partes del nombre Dim nNombreCompleto() As String Dim NombreFinal() As String Dim partes As Integer, Apellido As String 'Obtenemos una matriz con las partes descompuestas nNombreCompleto = Split(N_Apellido, "|") 'conteo de las partes del Nombre y Apellidos descompuesto 'debe ser mínimo 3 partes := Nombre + Apellido1 + Apellido2 'pero podría devolvernos 4 partes o más en caso de Nombre compuesto! partes = UBound(nNombreCompleto) + 1 'Recomponemos Nombre y Apellidos ReDim NombreFinal(0 To partes) As String If partes > 3 Then 'esto nos indica que el Nombre es Compuesto! 'Juntamos las dos primeras partes como un único elemento NombreFinal(0) = nNombreCompleto(0) & " " & nNombreCompleto(1) contador = 1 'el resto los cargamos tal cual... For x = 2 To UBound(nNombreCompleto) NombreFinal(contador) = nNombreCompleto(x) contador = contador + 1 Next x Else 'si el Nombre NO es compuesto 'cargamos uno a uno las partes del nombre For x = 0 To UBound(nNombreCompleto) NombreFinal(contador) = nNombreCompleto(x) contador = contador + 1 Next x End If 'devolvemos el nombre completo a la funcion 'OJO!! EJECUTARLA MATRICIALMENTE PARA OBTENER EN CADA CELDA EL DATO CORRESPONDIENTE!! NombreApellidos = NombreFinal End Function
3. Usando lenguaje M en Power Query
Si has usado Power Query para tratar datos, entonces ya has trabajado con lenguaje M aunque no te des cuenta. Power Query generar pasos y esos pasos a su vez generar un código M que fácilmente puede ser manipulado si cuentas con los conocimientos.
Antes de ponerte el script en M, sigue los siguientes pasos:
-
Convierte a Tabla tu rango con los nombres completos.
-
Dale el nombre de Tabla1.
-
Asegúrate que el encabezado de la tabla sea Nombre completo.
-
Elije tu Tabla1 y ve a Datos > Obtener y transformar > Desde una tabla o rango.
-
Al abrirse Power Query, no hagas ninguna transformación.
-
Elige Archivo > Cerrar y cargar en > Crear sólo conexión.
Una vez que se haya creado la conexión:
-
Nos vamos nuevamente a Datos > Obtener y transformar > Desde otras fuentes > Consulta en blanco.
-
En la pestaña Inicio elegimos Editor avanzado y pegamos el siguiente código.
let /*Cargando datos de una tabla en la hoja de cálculo*/ //reemplazos = TablaReplacements, //ListaReemp = Table.AddColumn(reemplazos, "Lista", each ({[Reemplaza], [por]})), /*Cargando datos de la lista creada en M */ ListaReemp=({{"De|","de"},{"Del|","del"},{"El|","el"},{"La|","la"},{"Las|","las"},{"Los|","los"},{"San|","san"},{"Y|","y"}}), /*Cargamos la tabla con los nombres completos a trabajar*/ NombresCompletos = Tabla1, /*Aplicamos formato de 'Nombre propio' */ MayusculasCadaPalabra = Table.TransformColumns(NombresCompletos,{{"Nombre completo", Text.Proper, type text}}), /*Separamos cada nombre en una lista, usando el espacio en blanco... cada palabra le incorporamos un caracter | */ TurnTextToList = Table.AddColumn(MayusculasCadaPalabra, "Split1", each List.Transform(Text.Split([Nombre completo], " "), each _ & "|")), /*Reemplazamos las coincidencias de ListaReemp y unimos de nuevo los elementos separados en el paso anterior */ Replacements = Table.AddColumn(TurnTextToList, "Nombre_1", each Text.Combine(List.ReplaceMatchingItems([Split1],ListaReemp)," ")), /*Eliminamos el último caracter | */ MenosUltimoCaracter = Table.AddColumn(Replacements, "Nombre_2", each Text.Start([Nombre_1],Text.Length([Nombre_1])-1)), /*Separamos nuevamente por el separador | */ Ultimo_Split = Table.AddColumn(MenosUltimoCaracter, "UltimoSplit", each Text.Split([Nombre_2],"|")), /*Contabilizamos el número de elementos en cada nombre */ Cuenta_Partes = Table.AddColumn(Ultimo_Split, "CuentaElementos", each List.Count([UltimoSplit])), /*Y añadimos tres columnas Nombre, Apellido1 y Apellido2 en base al recuento anterior. */ AddNombre = Table.AddColumn(Cuenta_Partes, "Nombre", each if [CuentaElementos]>3 then [UltimoSplit]{0}? & [UltimoSplit]{1}? else [UltimoSplit]{0}?), Add1erApellido = Table.AddColumn(AddNombre, "1er Apellido", each if [CuentaElementos]>3 then [UltimoSplit]{2}? else [UltimoSplit]{1}?), Add2doApellido = Table.AddColumn(Add1erApellido, "2do Apellido", each if [CuentaElementos]>3 then [UltimoSplit]{3}? else [UltimoSplit]{2}?), /*Eliminamos columnas que nos 'sobran' */ ColumnasQuitadas = Table.RemoveColumns(Add2doApellido,{"Nombre completo", "Split1", "Nombre_1", "Nombre_2", "UltimoSplit", "CuentaElementos"}) in ColumnasQuitadas
Descargar el archivo de ejemplo
Separar nombes y apellidos compuestos LET, LAMBDA, VBA y M – EXCELeINFO.zip
Si te gustó entrevista, por favor regístrate en nuestra Lista de correo y Suscríbete a nuestro canal de YouTube para que estés siempre enterado de lo nuevo que publicamos.
