Cuando necesitamos separar de una celda los apellidos, resulta imposible debido a los apellidos compuestos, para ello necesitamos una macro para separar apellidos compuestos en Excel.
Mediante las macros podemos identificar si el apellido es compuesto al incluir el conector o prefijo: "de", "del", "de la", "de los", "la", "las", "los", "san", "y", "-", etc. Reemplazando el espacio por un carácter especial, posteriormente aplicamos texto en columnas.. Según el idioma se puede ampliar:
- Alemán: Am, Aus'm, Vom, Zum, Zur.
- Francés: La, Le, Du, Des, D’, de.
- Holandés: Ver, De, Den, op de, ter, Van, Van der, ten, Van 't.
- Italiano: A, D', Da, De, Del, Della, Di, Li, Lo.
- Inglés: D', De, De la, Du, Le, Van, Von.
- Portugués: Da, Do, Das, Dos, De.
En la Columna A se encuentra el nombre de nuestros clientes y en la columna B sus dos apellidos y deseamos separar los apellidos, teniendo en cuenta los apellidos compuestos.
| A | B | |
|---|---|---|
| 1 | Nombre | Apellidos |
| 2 | Ramón | Pérez de Ayala y Fernández |
| 3 | Joaquín | Ricardo-García Ruiz |
| 4 | Mariano José | de Larra y Sánchez de Castro |
| 5 | Ramón | Gómez de la Serna y Puig |
| 6 | Miguel | de la Quadra Salcedo |
| 7 | María | de los Santos Granados |
| 8 | Rosa | de las Heras Ramos |
| 9 | Ana | Pérez San Fernando |
CREAR LA FUNCIÓN
Con Excel abierto.
Pulsar la tecla Alt y mantener pulsada
Pulsar la tecla F11. Mostrará el editor de Visual Basic.Ir a: Barra de Menú
Insertar 
Módulo. Mostrará la ventana del módulo donde escribimos la función.Escribir el código:
Function ApellidoCompuesto(MiTexto As String) As String
Dim Apellido() As String
Dim NuevoApellido As String
Dim i As Integer
Apellido = Split(Trim(MiTexto))
For i = 0 To UBound(Apellido)
Select Case LCase(Apellido(i))
Case "de", "del", "de la", "de los", "la", "las", "los", "san", "y", "-"
NuevoApellido = NuevoApellido & Apellido(i) & " "
Case Else
NuevoApellido = NuevoApellido & Apellido(i) & "/"
End Select
Next
If Right(NuevoApellido, 1) = "/" Then
NuevoApellido = Left(NuevoApellido, Len(NuevoApellido) - 1)
End If
ApellidoCompuesto = NuevoApellido
End Function
- "/". Caracter que insertará entre apellidos.
- Conectores de unión: "de", "del", "de la", "de los", "la", "las", "los", "san", "y", "-"
Guardar el libro con la extensión *.xlsm. Libro habilitado para macros.
INSERTAR FUNCIÓN
Clic sobre la celda C2. Celda donde insertar la función.
Ir a: Cinta de opciones
Pestaña: Fórmulas Grupo: Biblioteca de funciones Insertar función; O también, Barra de fórmulas Insertar función. Mostrará la ventana de Insertar función.
Ir a: O seleccionar una categoría
Seleccionar: Definida por el usuario.Ir a: Seleccionar una función
Clic sobre ApellidoCompuesto. Nombre de la función insertada en la macro para la función.Pulsar el botón Aceptar. Cierra la ventana y muestra la ventana de argumentos de la función.
Ir a: MiTexto
Clic sobre la celda B2. Celda con los apellidos que deseamos separar.Pulsar el botón Aceptar. Cierra la ventana e inserta la función, mostrando el delimitador
entre apellidos.
Copiar la celdas B2. Copia con la función.
Seleccionar el rango B3:B9
Pegar la función. Incluyendo la función en el rango.PEGAR COMO VALOR. Seleccionar el rango B2:B9. Necesario para aplicar posteriormente texto en columnas.
Botón derecho
Copiar. Copiar el rango.Clic sobre la celda B2. Celda desde donde pegar el valor.
Botón derecho
Pegado especial Pegar como valor.
SEPARAR APELLIDOS
Seleccionar el rango B3:B9. Rango al que separar los apellidos.
Ir a: Cinta de opciones
Pestaña: Datos Grupo: Herramientas de de datos Texto en columnas 
. Mostrará el asistente de conversión.
Clic sobre el botón de opciones: Delimitado. Tipo deseado al no tener un número de caracteres único por donde separar.
Pulsar el botón Aceptar. Cierra la ventana y continua con el asistente.
Ir a: Separadores
Activar la casilla: Otros y Escribir: /. Símbolo insertado por la función por donde separar.Pulsar el botón Siguiente. Cierra la ventana y continua con el asistente.
Ir a: Destino
Clic sobre la celda E2. Desde donde separará en columna los apellidos.Pulsar el botón Finalizar. Cierra la ventana y aplica el texto en columnas.
Excel Experto VBA - Macros
hola una pregunta yo necesito programar en un botón que los apellidos se ordenen en diferentes celdas como le puedo hacer
Hola Alejandra,
La única solución es mediante macros, que asigne tus necesidades a la hora de colocar los apellidos como necesites.
Saludos.
BUENAS TARDES
UNA CUNSULTA
EXISTE ALGUNA MANERA PARA SEPARAR DIRECCION DEL NUMERO OFICIAL?
EJEMPLOS:
LUIS SPOTA 1374
PRV LIVERPOL 124
JOSE GARZA RAMIREZ 160-1
JOSE GARZA RAMIREZ 160-2
RUBEN DARIO 250 NTE
Hola Jimmy,
Puedes aplicar la herramienta de texto en columna.
Saludos.
No me sirvio la formula, nunca me aparecio la opción "Definida por Usuario" ni la función "ApellidoCompuesto"
Hola Juan,
El código es de una función VBA, y se mostrará en la categoría de las funciones: Definida por el usuario y en la función con el nombre de ApellidoCompuesto junto a otras funciones si dispones de ellas.
En caso contrario, el código está mal escrito.
Saludos.
Muchisimas gracias no sabes como me ha servido tu código. Un saludo bien grande