Trucos de Excel

NIVEL: AVANZADO

Descargar archivo de ejemplo.  Para que funcione hay que habilitar las macros.

En este tutorial veremos como programar una función en Excel que permitar concatenar fácilmente varios elementos al ingresar un rango de datos y no las celdas uno a uno como nos pide la función CONCATENAR en Excel.

No serán frecuentes los trucos publicados que involucren programación en Visual Basic (VBA) por dos motivos:

• El objetivo de este blog es aprender explotar las herramientas que ya cuenta el Excel y que en varios casos facilita el no tener que recurrir a programación.
• Fundamentalmente porque NO es mi fuerte la programación.  La enorme mayoría de situaciones que he tenido que resolver, he podido solicionarlas sin necesidad de recurrir a macros.

Pero siempre hay excepciones.  Y este caso es uno de ellos.

La función CONCATENAR es MUY limitada.  Debemos incluir una a una las celdas que se quieren combinar y no permite utilizar rangos.

Supongamos tenemos las siguientes listas:

Y queremos tener en una única celda, todas las ciudades de cada país combinadas.

Ejemplo, que para Brasil nos quede: “San Pablo, Belo Horizonte, Fortaleza, Salvador”.

La función que deberíamos aplicar sería:

• =CONCATENAR(B3;”, “;B4;”, “;B5;”, “;B6;”, “;B7;”.”)

Bastante engorroso especialmente si tuviéramos mucho más que cinco celdas para combinar.

Para complicar más la situación, no todos los países de este ejemplo tienen la misma cantidad de ciudades.

Por ejemplo, para México solo listé tres.  Si copiamos la función de arriba en la columna E, el resultado sería: “México, Monterrey, Guadalajara, , .”

No es lo que estamos buscando, pero para que quede mejor, habría que corregir la función a mano y escribir:

• =CONCATENAR(E3;”, “;E4;”, “;E5;”.”)

No es práctico.

En este tutorial veremos cómo solucionar esta situación al crear una función que solo con escribir la siguiente función, nos de la respuesta que queremos:

• =MULTCONCAT(B3:B7)

Antes de proseguir, una aclaración para los más puristas de programación: debe haber formas más correctas de escribir el código.  Sin embargo, está escrito de forma que sea fácil entender qué es lo que está haciendo.

El objetivo de la función que vamos a escribir, es que tome todos los datos que se encuentren en el rango dado y los combine uno tras otro, separándolos con “,” menos el último al cual le pondrá un “.”

También necesitamos que considere que no necesariamente la longitud del rango coincide con la cantidad de elementos.  Ejemplo, el rango E3:E7 contiene 5 celdas, pero solo 3 de ellas tienen ciudades y las otras dos están vacías.

1. Insertar un nuevo módulo para escribir el código.

En Excel 2007, vamos al menú Programador -> Visual Basic y sobre el nombre del libro de trabajo donde guardaremos la macro, hacemos clic derecho -> Insetar -> Módulo

2. Código de la función MULTCONCAT

MULTCONCAT es un nombre inventado.  Puede ser cualquiera (que no sea ya una función de Excel).

Éste es el código de la función y a continuación iremos paso a paso ver en qué consiste:

Function MULTCONCAT(lista As Range)

Dim ncell As Range

Dim m_concat As String

m_concat = ""

i = 1

For Each ncell In lista

  If ncell <> "" Then
    If i = 1 Then
      m_concat = m_concat & ncell.Value
    Else
      m_concat = m_concat & ", " & ncell.Value
    End If
  End If

i = i + 1

Next ncell

m_concat = m_concat & "."

MULTCONCAT = m_concat

End Function

3. Análisis del código

Fundamentalmente lo que hará la función MULTCONCAT:

• Recorrerá una a una las celdas que componen el rango que se pasa como parámentro (“lista”).
• En la variable m_concat, irá concatenando (a través de “&”) el valor que tenga junto con la nueva celda a leer.
• Adicionalmente, irá agregando una “, ” antes de agregar un nuevo elemento.

3.1 Definición de variables

Function MULTCONCAT(lista As Range)

Dim ncell As Range :

Dim m_concat As String

m_concat = ""

i = 1

Donde:

• En ncell tendremos cada celda que leeremos del rango ingresado como parámetro (en “lista” dentro de la función MULTCONCAT).
• m_concat es una variable de texto (string) donde iremos concatenando una a una las celdas
• i : simplemente un contador para indicarnos si es el primer valor de la lista.  Solo lo utilizo para NO poner la “, ” delante.

3.2 LOOP.  Armado de la cadena Concatenada

For Each ncell In lista

  If ncell <> "" Then
    If i = 1 Then
      m_concat = m_concat & ncell.Value
    Else
      m_concat = m_concat & ", " & ncell.Value
    End If
  End If

i = i + 1

Next ncell

Para cada celda en el rango “lista”, siempre y cuando ésta contenga un valor (<>”") hará:

• Si i=1 (es el primer valor), solamente agrega el valor de esa celda (ncell.Value).
• De lo contrario (es decir, ya tenemos al menos UN elemento), tomará la cadena que ya esté armada, le agregará la “, ” y agregará el valor de una nueva celda.
• Se incrementa el contador (i).  Solamente es para romper el indicador de primera posición.  Ya no tiene más uso.

La razón por la que agregamos la “, ” en este lugar, es para evitar que la ponga cuando hay celdas vacías o si tenemos rangos que tienen un único valor. También se requiere que sea de esta forma dado que se pondrá un punto “. ” al final, en lugar de una coma.

3.3 Cierre de la función

m_concat = m_concat & "."

MULTCONCAT = m_concat

End Function

Acá solamente se agrega un “. ” para cerrar la cadena y se asigna a la función MULTCONCAT la cadena que se fue armando (m_concat).

La función está lista para utilizar.  Ahora, si hacemos MULTCONCAT(B3:B7), nos devolverá “San Pablo, Belo Horizonte, Fortaleza, Salvador, Brasilia.”

La fórmula MULTCONCAT(E3:E7) dará como resultado “México, Monterrey, Guadalajara.”

———————————————————————————————————————————————-

¿Te gustó este truco o tenés alguna recomendación u otra forma de hacerlo?  No te olvides de dejar tu comentario y compartir esta nota!

Buscá más trucos en: trucosdeexcel.com
Seguime por Twitter, usuario “trucosdeexcel”.
Mandá tus sugerencias o preguntas a: excelman@trucosdeexcel.com

Más opciones para compartir !!

17 comentarios para “CONCATENAR MULTIPLE – Función escrita en VBA”