Registrar entradas con fórmulas – una aclaración

En la nota sobre registro de entradas con fórmulas en Excel, sugerí el uso de Tablas para evitar tener que copiar las fórmulas con cada entrada.

Sin embargo, y como me señalan varios de mis lectores, el uso de tablas en este caso puede dar resultados inesperados. Veamos:

Al introducir el segundo nombre nos hemos movido con las flechas y el resultado es 0, que por el formato fecha-hora vemos como 00/01/1900 00:00:00.

Sin embargo, si nos movemos con la tecla TAB veremos que el resultado es el correcto:

En conclusión: podemos usar un rango común de Excel, copiando las fórmulas o convertir el rango en Tabla y movernos dentro de esta con la tecla TAB.

Registrar fecha y hora de una entrada en Excel con fórmulas

Poniéndome al día con los 196 mails acumulados durante mis vacaciones, me encuentro con esta consulta:

quiero capturar la hora y la fecha del momento que ingresar este información, la función HOY() y AHORA() no sirven por que estas se actualizan cada vez que ingreso datos en la hoja; es necesario para mi que estas queden estáticas para realizar posteriores cálculos y estadísticas con ellas…

Mi primera respuesta fue que la única forma de hacerlo es programando un evento (macro). Pero después de investigar un poco el tema descubrí (y muchos otros antes de mi) que puede hacerse con fórmulas.

Para hacerlo con fórmulas tenemos que crear una referencia circular, tema mencionado tangencialmente en la prehistoria de este blog en esta nota.

En pocas palabras, ¿qué es una referencia circular? Cuando una fórmula incluye una referencia a la misma celda que la contiene o una celda que se refiere a ésta, Excel genera una advertencia de "referencia circular".

En este ejemplo, queremos calcular la ganancia neta que incluye el pago de comisiones que a su vez son calculadas en base a la ganancia neta, creándose así una referencia circular (la celda B3 contiene la fórmula =B4*15% y la celda B4 la fórmula =B1-B2-B3 que se refiere a la celda B3)

Al apretar Aceptar veremos

Para que Excel pueda resolver el cálculo tenemos que habilitar el cálculo iterativo en Opciones de Excel-Fórmulas

Después de habilitar el cálculo iterativo Excel muestra el resultado

Una vez definido el cálculo iterativo podemos usar esta fórmula para crear una registro de fecha y hora ("timestamp" en inglés)

=SI(A2<>"",SI(B2="",AHORA(),B2),"")

Para evitar tener que copiar la fórmula a lo largo de la columna A, transformamos el rango en Tabla (en un rango definido como Tabla las fórmulas y formato de las columnas son copiadas automáticamente)

26/10/2012 - Aclaración importante sobre el uso de Tablas en este modelo.

En la próxima nota veremos como hacerlo programando un evento (macros).

El extraño caso del espacio inamovible en Excel (o ASCII 160)

Cuando importamos datos a una hoja de Excel, en particular de una base de datos o de la Web, las celdas pueden contener, además del valor visible, un espacio o algún carácter no imprimible.

La presencia de estos caracteres crea varios problemas: los números son interpretados por Excel como texto o los resultados de filtrar u ordenar resultan imprevisibles.
Excel nos provee con dos funciones para enfrentarnos con estos problemas: ESPACIOS() y LIMPIAR().

ESPACIOS() remueve todos los espacios excepto los espacios individuales entre palabras. Pero no siempre es así. Veamos este ejemplo

La celda A2 contiene tres caracteres visibles (abc) pero la función LARGO da un valor de 4. Este se debe a que la celda contiene un espacio en blanco después de "c".

En la celda A3 usamos ESPACIOS() para remover el espacio y vemos que ahora el largo es de 3.

Ahora veamos este caso, aparentemente idéntico

A pesar de que ambos casos parecen idénticos, en este segundo caso LIMPIAR no logra remover el espacio en blanco. Esto se debe a que la función LIMPIAR fue diseñada para remover el carácter de espacio de 7-bit ASCII (valor 32). Pero ciertos programas, y en especial datos provenientes del Web usan el carácter ASCII 160. Este carácter se utiliza comúnmente en las páginas Web como la entidad HTML. La función LIMPIAR no quita este carácter de espacio.

Una solución es usar la función SUSTITUIR()

=SUSTITUIR(A2;CARACTER(160);"")

Si se trata de un número (que ha sido convertido en texto por la presencia del espacio), agregamos un doble signo menos (--) al principio de la fórmula para forzar la conversión a número

El uso de la función SUSTITUIR puede ser menos conveniente cuando tenemos que ocuparnos de un gran número de registros. En esos caso es mejor usar una macro como ésta

Sub limpiar_todo()

    Selection.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
       
End Sub

Esta macro quita todos los espacion generados por el carácter 160 en el rango seleccionado. Si queremos quitar todos los espacios en la hoja usamos

Sub limpiar_todo()

    Cells.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
       
End Sub

Búsquedas con COINCIDIR en varias columnas

A partir de mañana y por los próximos diez estaré ocupado en una investigación sobre el tema “Los mejores platos y vinos en restoranes no turísticos de Toscana” (es decir, me tomo vacaciones en esa bellísima zona de Italia).

Mientras tanto veamos un tema sobre el cual he recibido varias consultas últimamente: realizar búsqueda usando COINCIDIR a través de varias columnas.

Para calcular qué posición ocupa un elemento determinado en un rango, Excel nos provee con la función COINCIDIR. Pero si el rango de búsqueda comprende más de una columna (o fila), la función da un resultado de error.

Para el caso supongamos que tenemos una serie de valores (números o texto) en el rango D1:E10. Para saber que posición ocupa el valor “14” nos veremos tentados a usar la fórmula

=COINCIDIR(B2,D1:E10,0)

donde B2 contiene el valor de búsqueda. Si bien 14 ocupa el quinto lugar en la segunda columna, el resultado es #N/A

La función COINCIDIR funciona sólo con rangos de búsqueda (matrices) de una única columna o fila.

Para hacer la búsqueda a través de varias columnas podemos combinar COINCIDIR con las funciones SI y ESERROR o, si usamos Excel 2007 o 2010, la nueva función SI.ERROR de esta manera

=SI.ERROR(COINCIDIR(B2,D1:D10,0),SI.ERROR(COINCIDIR(B2,E1:E10,0),"inexistente"))

Si la búsqueda debe hacerse en tres columnas agregamos otra función SI.ERROR

=SI.ERROR(COINCIDIR(B2,D1:D10,0),SI.ERROR(COINCIDIR(B2,E1:E10,0),SI.ERROR(COINCIDIR(B2,F1:F10,0),"inexistente")))

En Excel Clásico (97-2003) puede hacerse combinando Si con ESERROR, pero la nueva función SI.ERROR nos permite crear una fórmula mucho más compacta.

Uno de los inconvenientes de esta solución es que no nos dice en qué columna se encuentra el valor. Además, nos da la ubicación relativa del elemento en la matriz de búsqueda, pero por lo general queremos saber en qué fila se encuentra el elemento.

Para calcular la dirección de la celda que contiene el elemento buscado podemos usar DIRECCION combinada con la solución anterior

=SI.ERROR(DIRECCION(COINCIDIR(B2,D1:D10,0),4),SI.ERROR(DIRECCION(COINCIDIR(B2,E1:E10,0),5),"inexistente"))

Otra alternativa es crear una UDF (función definida por el usuario) como ésta

Function direccion_celda(Valor_Buscado, Matriz_Busqueda As Range)
    Dim rngCell As Range
   
    For Each rngCell In Matriz_Busqueda
        If rngCell.Value = Valor_Buscado Then
            direccion_celda = rngCell.Address
            Exit Function
        Else
            direccion_celda = "inexistente"
        End If
    Next rngCell

End Function

EL extraño caso del signo más (+) en Excel.

Excel tiene ciertas zonas un poco tenebrosas donde no todo funciona, o parece funcionar, como esperamos. Pero el usuario avisado puede evitar entrar en esos oscuros callejones…

Vimos el caso de los resultados incorrectos de CONTAR.SI. Hoy vamos a ver cierto comportamiento extraño del símbolo “más” (+).

Todos sabemos que si iniciamos una fórmula con el “más” (+) Excel lo transforma automáticamente en “igual” (=). El símbolo + era el usado en Lotus 1-2-3, el antecesor de Excel.

Por ejemplo, si introducimos en una celda “+10/2.5”, veremos en la barra de fórmulas que Excel lo transforma en “=10/2.5” y el resultado es 4



Ahora veamos que pasa con esta operación cuando la celda tiene un formato distinto al General



Excel graciosamente nos informa que hay un error en la fórmula. No voy a intentar explicar este fenómeno por la sencilla razón que no tengo la menor idea por qué sucede. Pero si puedo exponer la solución.

En Excel 2010 activamos Archivo-Opciones-Avanzada y al final del formulario señalamos la opción “Introducción de fórmulas de transición” y apretamos Aceptar.

En Excel 2007

En Excel 2003

Ordenar listas numéricas con fórmulas en Excel

Nada más sencillo que ordenar listas o tablas en Excel. Un clic al icono correspondiente (ascendente o descendente) y Excel ordena la lista. Pero en ciertas situaciones queremos que la lista se ordene automáticamente al ir agregando o quitando datos.

Podemos programar un evento que se ocupe de ordenar nuestra lista con cada cambio, pero también podemos hacerlo con fórmulas. En esta nota veremos cómo hacerlo en el caso de listas numéricas (cómo hacerlo con listas de texto he mostrado en esta nota).

Supongamos un rango donde vamos agregando fechas (recordemos que la fechas son números en Excel)

Como puede verse estoy usando ALEATORIO.ENTRE para generar fechas en forma aleatoria.

Para ordenar esta lista en orden ascendente usamos esta fórmula

=K.ESIMO.MENOR(lstFechas,FILA()-1)

donde “lstFechas” es un nombre que define el rango de las fechas en forma dinámica, con la fórmula

=fechas!$A$2:INDICE(fechas!$A:$A,CONTARA(fechas!$A:$A))

Para ordenar la lista en orden descendente usamos esa otra fórmula

=K.ESIMO.MENOR(lstFechas,CONTARA(lstFechas)-FILA()+2)

Una desventaja de este método es que cada vez que agregamos un valor a lista debemos copiar las fórmulas. Podemos superar este inconveniente convirtiendo el rango en “tabla”



Al convertir el rango en tabla, las fórmulas son copiadas automáticamente.

Cálculo de semanas en Excel

Excel no tiene una función que calcule la cantidad de semanas entre dos fechas. Para calcular la cantidad de años, meses y días podemos usar la “indocumentada” función SIFECHA.

En esa nota muestro cómo calcular también la cantidad de semanas, pero dado que sigo recibiendo consultas sobre el tema mostraré aquí la técnica en forma sucinta. A quien no esté familiarizado con SIFECHA le recomiendo leer la nota mencionada.

Supongamos que queremos calcular la cantidad de meses, semanas y días entre dos fechas

En la celda B4 usamos la fórmula

=SIFECHA(comienzo,fin,"ym")

donde “comienzo” es un nombre que se refiere a la celda B1 y “final” es un nombre que se refiere a la celda B2.

(aclaración: dependiendo de las definiciones del sistema hay que usar “a” en lugar de “y” para los años)

En B5 ponemos =SIFECHA(comienzo,fin,"md")

Finalmente en B8 usamos =B4&" meses y "&B5&" días"

Excel no incluye el día de comienzo cuando calcula el intervalo, de manera que quien quiera incluirlo en el resultado deberá agregar “+1” a la fórmula.

Nosotros queremos este cálculo

En la celda B5 (semanas) ponemos

=ENTERO(SIFECHA(comienzo,fin,"md")/7)

para calcular la cantidad de semanas de los días no incluidos en la cuenta de los meses.

En la celda B6 ponemos

=SIFECHA(comienzo,fin,"md")-(B5*7)

Es decir, descontamos del total de días calculado por SIFECHA la cantidad de días de las semanas en la celda B5.

Si queremos una fórmula que no dependa del B5 tendríamos que usar

=SIFECHA(comienzo,fin,"md")-(ENTERO(SIFECHA(comienzo,fin,"md")/7)*7)

La fórmula en B9 es

=B4&" meses, "&B5&" semanas y "&B6&" días"

Uso de comodines (wildcards) en funciones de Excel

Cuando filtramos tablas o hacemos búsquedas en Excel podemos usar comodines (*, ?). ¿Podemos usarlos en las funciones? Bien, sí y no. Es decir, hay funciones que aceptan comodines en los argumentos y otras que no.

Por ejemplo, queremos evaluar si una celda contiene cierto texto. En caso afirmativo el resultado será "A", en caso negativo "B".

Obviamente =SI(A1="*no*";"A";"B") no funciona.

Pero veamos esta alternativa

Como pueden ver, =SI(ESNUMERO(HALLAR("no";A1));"A";"B") no requiere comodines para nuestro ejemplo.

Podemos ver cómo funciona esta fórmula descomponiéndola en sus partes:

La función HALLAR da la primera posición en la cadena de texto del texto buscado

=HALLAR("no";A2)

Si el texto no aparece el resultado el #¡VALOR!

La función ESNUMERO evalúa si el resultado de HALLAR es numérico. Cuando lo es da VERDADERO, cuando no lo es, también si el error, da FALSO. Estos resultados son los argumentos que usamos en SI.

Veamos un ejemplo más elaborado donde buscamos valores que en la segunda posición contienen una "n" y en la cuarta posición una "o".

Como ven, en =SI(ESNUMERO(HALLAR("?n?o";A2));"A";"B") estamos usando el comodín "?" para señalar que la primera y la tercera posición puede ser ocupada por cualquier valor, pero la segunda y la cuarta deben ser "n" y "o" respectivamente.

La función COINCIDIR también acepta comodines.

También la función CONTAR.SI. Por ejemplo, en la lista anterior, si queremos contar todos los nombres que terminan con la letra "o" usamos

=CONTAR.SI($A$2:$A$7;"*o")

De la misma manera SUMAR.SI comodines pero SUMAPRODUCTO no.

Determinar posición (ranking) por grupos

En la nota sobre la función JERARQUIA aparece este comentario

¿Es posible aplicar la función jerarquía a una columna de manera que se reinicie el ranking si el valor de otra columna cambia? Por ejemplo, tengo 5 vendedores en 4 países y quiero saber el principal vendedor en cada país.

Esta consulta me llevó a reflexionar sobre dos temas:

1 - ¿cómo aplicar la función JERARQUIA en este caso?
2 – ¿Por qué aplicar la función JERARQUIA para este caso?

Muy a menudo me encuentro con consultas de este tipo, donde el lector presupone que la solución pasa por usar una función o un método determinado. En lugar de preguntar "¿cómo determino el mejor vendedor de cada país?" la consulta se transforma en "¿cómo uso JERARQUIA para solucionar este problema?

El medio (el uso de la función JERARQUIA) se transforma en el objetivo (encontrar el mejor vendedor de cada país).

Supongo que esta forma de enfrentar la solución de problemas está relacionada con la tendencia natural, en mi opinión, de aferrarnos a lo conocido y nuestra aversión o temor a lo desconocido. Seguramente hay otros factores, pero no lo trataré en esta nota. Por supuesto, mis lectores están invitados a opinar sobre el tema.

Y ahora llegó el momento de empezar a trabajar. ¿Cómo solucionamos el problema? Empecemos por mostrar nuestros datos

Solución con JERARQUIA



Para usar la función JERARQUIA por grupos (vendedores de Argentina, vendedores de Colombia, etc.) empezamos por definir rangos con nombres

Cada nombre se refiere al rango de valores de ventas correspondiente al país a que se refiere. Nótese que estos rangos no son dinámicos, por lo que se agregamos valores, tendremos que editar el nombre para redefinir el rango.

Agregamos una columna a la tabla, Ranking, donde ponemos esta fórmula

=JERARQUIA(C2,INDIRECTO(A2))

Hemos agregado además un formato condicional para resaltar el nombre y las ventas del vendedor que recibe la posición 1

Solución con INDICE, COINCIDIR y MAX en forma matricial

La ventaja de esta solución es que nos permite definir rangos dinámicos, librándonos de la necesidad de editar los rangos definidos cada vez que agregamos o quitamos datos de la tabla.

Otra ventaja es que implementamos uno de los principios importantes de buenas prácticas en Excel: la separación de la base de datos de los cálculos y los informes.

Como en el caso anterior, empezamos por definir rangos en nombres (otra buena práctica en Excel)
En este caso definimos rangos dinámicos

pais =indice!$A$2:INDICE(indice!$A:$A,CONTARA(indice!$A:$A))
vendedor =indice!$B$2:INDICE(indice!$B:$B,CONTARA(indice!$B:$B))
ventas =indice!$C$2:INDICE(indice!$C:$C,CONTARA(indice!$C:$C))

Creamos una tabla separada para mostrar los resultados por país donde ponemos esta fórmula matricial (fórmulas que introducimos apretando simultáneamente Ctrl+Mayúsculas+Enter)

=INDICE(vendedor,COINCIDIR(MAX((pais=E2)*(ventas)),ventas,0))

Solución con tablas dinámicas

Empezamos por crear una tabla dinámica con los campos País y Vendedor en el área de filas y Ventas en el área de datos. Luego ordenamos el campo Vendedor según las Ventas

El próximo paso es crear un campo calculado, Ranking (o cualquier otro nombre) con la fórmula "=1"

Seleccionamos el campo que acabamos de agregar y en el menú de configuración del campo mostramos los valores como "Total en" usando como campo de base "Vendedor"

El resultado es

Como puede apreciarse, no se trata de una verdadera solución ya que nos basta con ordenar los valores tal como hicimos en el primer paso. Obviamente, quien aparece en el primer lugar de cada país es el que más vendió. Esta solución puede ser útil si queremos extraer el vendedor que más vendió (o el segundo o el tercer, etc.) usando funciones que se refieran a la tabla dinámica, como IMPORTARDATOSDINAMICOS.

En este enlace muestro otra forma de aplicar JERARQUIA, es decir señalar el ranking, en una tabla dinámica.

Encontrar el último número positivo o negativo en un rango

En uno de los proyectos que estoy desarrollando me enfrenté con la necesidad de encontrar el último número negativo de una serie. Específicamente se trataba de calcular el período de recuperación de una inversión (Payback), pero este problema puede presentarse en varias situaciones.

En el pasado he mostrado cómo encontrar el último elemento en un rango usando la función BUSCAR (LOOKUP). Pero en este caso se trata de encontrar el último elemento bajo la condición que sea negativo.

Supongamos esta serie de números en el rango B1:B9

Nuestro objetivo es crear una fórmula que de cómo resultado el último número negativo de la serie, en nuestro caso -30

La fórmula que usamos es la siguiente:

=BUSCAR(2;1/(B1:B9<0);B1:B9) 

¿Cómo funciona esta fórmula?
 
La función BUSCAR tiene dos configuraciones: la vectorial y la matricial. Más sobre el tema puede leerse en la ayuda en línea de Excel. En nuestro caso usamos la forma vectorial que tiene esta sintaxis:

BUSCAR(valor_buscado, vector_de_comparación, [vector_resultado])

En nuestro ejemplo, el vector de comparación es creado por la expresión 1/(B1:B9<0) Esta resulta en una serie de unos y valores de error. 

Cuando el valor de la celda de la columna B es negativo el resultado es 1 (1/1); cuando es positivo el resultado es #DIV0! (1/0).
A pesar de lo que dice la ayuda en línea de Excel (Cuando BUSCAR no puede encontrar el valor buscado, la función muestra el valor más grande en vector_de_comparación que es menor o igual al valor_buscado), BUSCAR da el último valor que es menor o igual al buscado. Por este motivo usamos “2” como valor buscado. De la misma manera podríamos usar 3 o cualquier otro número mayor que 1. 


Para calcular el último número positivo en la serie modificamos levemente nuestra fórmula 

=BUSCAR(2;1/(B1:B9>=0);B1:B9)

Calcular el mínimo con criterios excluyendo ceros

Un lector me consultaba cómo calcular el precio mínimo de los tornillos en esta lista

Para hacerlo tenemos que crear una matriz (array) de valores de la lista que correspondan a la categoría tornillos. Esta lista la usamos como argumento en la función MIN para obtener el valor buscado. La fórmula matricial sería

=MIN((A2:A13="Tornillos")*B2:B13)

Esta es una fórmula matricial que introducimos apretando simultáneamente Ctrl+Mayúscula+Enter.

El problema con esta fórmula es que cuando un valor en la lista no corresponde a la categoría buscada ("tornillos" en nuestro caso), el valor en la matriz es 0 (cero) y éste pasa a ser el valor mínimo (a excepción de que la lista contenga valores negativos).

La solución es usar la función SI para crear una condición que excluya los ceros. La fórmula matricial que excluye los ceros del cálculo del mínimo es

=MIN(SI((A2:A13="Tornillos"),B2:B13))

Nótese que excluimos el tercer argumento de la función SI, es decir, ponemos solamente el resultado a calcular cuando la condición se cumple.

Los precios máximos se calculan sin mucho trámite con esta función matricial

=MAX((A2:A13="Tornillos")*(B2:B13))

Ahora vamos a darle un toque un poco más profesional a la solución agregándole la posibilidad de elegir el tipo de artículo en forma dinámica. Para esto creamos una lista desplegable con validación de datos en la celda E3

Nuestro modelo se ve ahora así

donde hemos modificado las fórmulas de esta manera

=MAX((A2:A13=E3)*(B2:B13))

=MIN(SI((A2:A13=E3),B2:B13))

Ahora, si queremos realmente impresionar al jefe (o mejor aún, a la secretaria) hacemos que también el tipo de valor buscado se establezca dinámicamente.

Agregamos dos columnas a la izquierda de la columna A, donde haremos cálculos auxiliares

En la celda A7 ponemos esta fórmula

=COINCIDIR(G2,A3:A4,0)

En las celdas A9 y A10 ponemos las fórmulas de máximo y mínimo respectivamente.

En G2 ponemos una lista desplegable con validación de datos con dos posibilidades: Máximo y Mínimo.

Finalmente, en la celda G4 ponemos la fórmula

=INDICE(A9:A10,A7)

Ahora, cuando el usuario elige el máximo, la celda A7 recibe el valor 1; si elige el mínimo la celda muestra el 2. Este valor es usado como argumento en la función INDICE de la celda G4, que extrae el valor de la celda A9 o A10, según el caso.

El último toque es ocultar la columna A.

El archivo con el ejemplo se puede descargar aquí.

Extraer las iniciales de un nombre con Excel

Uno de mis lectores me consulta cómo extraer las iniciales de un nombre. En la columna A tenemos una lista de nombres (empleados de una empresa, invitados a un acontecimiento, etc.) y en la columna B queremos obtener las iniciales

En esta nota presentaré dos soluciones, una basada en funciones y la segunda con una UDF (función definida por el usuario).

Solución con funciones

Empecemos por el final: la fórmulas es (respirar hondo) ésta

=ELEGIR(LARGO(A2)-LARGO(SUSTITUIR(A2," ",""))+1,IZQUIERDA(A2,1),IZQUIERDA(A2,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1),IZQUIERDA(A2,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",2))+1,1),IZQUIERDA(A2,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",2))+1,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",3))+1,1))

¿Cómo funciona esta fórmula?

La expresión

LARGO(A2)-LARGO(SUSTITUIR(A2," ",""))+1

calcula de cuantas palabras está compuesto el nombre. La primer función LARGO da la cantidad de caracteres en la celda, incluidos los espacios. La segunda función LARGO calcula la cantidad de caracteres sin los espacios. La diferencia entre ambas más 1, es el número de palabras.

De acuerdo a este valor, la función ELEGIR activa la expresión adecuada para extraer las iniciales.

La primer inicial será siempre la primer letra del nombre, lo que logramos con la fórmula IZQUIERDA(A2,1) . Este es el primer caso de la función ELEGIR.

Para encontrar la segunda inicial tenemos que encontrar donde comienza la segunda palabra. El truco aquí es reemplazar el primer espacio con un carácter especial (por ejemplo #). Para sustituir el primer espacio con el carácter # usamos

SUSTITUIR(A2," ","#",1)

Para hallar donde se encuentra este carácter en el nombre usamos

HALLAR("#",SUSTITUIR(A2," ","#",1))+1

Y finalmente, para extraer la inicial usamos

EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1)

Ahora tenemos que concatenarlo con la primer inicial lo que hacemos con el operador &

IZQUIERDA(A2,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1)

Para el caso que el nombre tenga tres palabras usamos la misma fórmula concatenando a la anterior la expresión

EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",2))+1,1)

Quedando

IZQUIERDA(A2,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",2))+1,1)

Para el caso de cuatro palabras en el nombre concatenamos a la expresión anterior

EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",3))+1,1)

Esta fórmula tiene algunas serias limitaciones:

• Puede extraer iniciales para nombres de hasta cuatro palabras (si bien podemos agregar otras expresiones y adaptarla para cinco o más palabras en el nombre)

• En caso de nombres que incluyen "de", la función falla ya que no distingue entre minúsculas y mayúsculas

• Otro problema surge si entre las palabras del nombre hay más de un espacio.

Una función definida por el usuario nos proporciona una solución más flexible (y elegante, en mi opinión).

Solución con una UDF

En un módulo común del editor de Vba (preferentemente en el Personal.xls) ponemos este código



Option Explicit

Function Iniciales(strText As String)
    Dim strLen As Long, iChr As Long
    Dim strTemp As String

    strLen = Len(strText)

    For iChr = 1 To strLen
        If Asc(Mid(strText, iChr, 1)) >= 65 And Asc(Mid(strText, iChr, 1))<= 90 Then
            strTemp = strTemp & Mid(strText, iChr, 1)
        End If
    Next iChr

    Iniciales = strTemp
    
End Function

Este código empieza por contar cuantos caracteres hay en la celda que contiene el nombre. Luego evalúa cada carácter y determina si es una letra mayúscula (Asc>=65 o Asc<=90). Si es mayúscula agrega el carácter a la variable strTemp. Al terminar la rutina, la función recibe el valor de strTemp.