jueves, 16 de febrero de 2017

Alternativas a la función UNIRCADENAS de Excel 365

Recientemente una alumna, con una versión de escritorio de Excel 2016, preguntaba por la posibilidad de concatenar fácilmente el contenido de varias celdas añadiendo un separador entre ellas...
Obviamente salió a la luz la existencia solo para la versión de Excel 3656 la función UNIRCADENAS, la cual ofrece y cubre la necesidad... pero ¿y que pasa con los usuarios de otras versiones de Excel?.
Improvisé rápidamente unas funciones anidadas para responder la duda.. aunque lógicamente tenía ciertas limitaciones.
Esta fue la función adaptada a su caso, para unir cuatro celdas:
=IZQUIERDA(A9&"|"&B9&"|"&C9&"|"&D9;LARGO(A9&"|"&B9&"|"&C9&"|"&D9)-(4-CONTARA(A9:D9)))



Como se observa, se une empleando el operador de unión ampersand (&) el contenido de las celdas, incorporando entre cada una de ellas un separador (la barra vertical |):
A9&"|"&B9&"|"&C9&"|"&D9

A partir de ese dato devuelto, se emplean las funciones de texto IZQUIERDA y LARGO junto a CONTARA, para eliminar el exceso de barras añadidas, provenientes de las celdas vacías.

Un problema añadido sería que en caso de que las celdas rellenas no fueran contiguas, el resultado obtenido, no sería de nuestro gusto.
Un último problema es la limitación del número de celdas, siendo necesaria una modificación de la formula en caso de requerir unir más de esas cuatro celdas.


Para evitar estos contratiempos, me he permitido definir con algo de programación, una función personalizada (UDF) en la ventana de código de un módulo estándar:

Function ConcatenaCeldas(rng As Range, Separador As Variant) As String
Dim Final As String
'recorremos las celdas del rango
For Each celda In rng
    'construimos el concatenado celda a celda
    'con la precaución de si la celda tiene o no contenido
    Final = Final + IIf(celda.Value = "", CStr(celda.Value), CStr(celda.Value) + Separador)
Next celda
'devolvemos el dato eliminando el último Separador añadido...
ConcatenaCeldas = Left(Final, Len(Final) - 1)
End Function



El resultado, como se vé en la imagen siguiente, rompe cualquier limitación anterior... permitiendo además elegir el separador a incluir:

Alternativas a la función UNIRCADENAS de Excel 365



Una ayuda para los que no trabajamos con Excel 365...

4 comentarios:

  1. Gracias por el foro y la ayuda.
    Legue aca buscando solucion a un problema, y llevo horas leyendo otras cosas muy interesantes, como esta funcion que me parece muy util.

    El problema (que aun no resuelvo) es un error 1004 al usar el metodo SaveAs desde una macro de excel 2003 protegida para visualizacion.
    Lo curioso es que el codigo funciona bien al quitar la proteccion, o si al menos ingreso 1 vez al editor de VB y coloco la contraseña. Pero si directamente quiero utilizar la macro, da error.

    Este es el codigo , que uso para generar un archivo plano .txt a partir de un listado de excel:

    Private Function grabar()
    ActiveWorkbook.Save
    Sheets("archivo").Visible = True
    Sheets("archivo").Select
    ActiveWorkbook.SaveAs Filename:= _
    ActiveWorkbook.Path & "\bna1.txt", FileFormat:=xlTextMac, _
    CreateBackup:=False
    ActiveWorkbook.Close SaveChanges:=False
    End Function

    Ya intente colocarlo dentro y fuera del modulo, como Sub o Function, etc. Como dije el codigo es simple y funciona perfecto, pero al proteger el proyecto para visualizacion, da error.

    Nota al margen, por si a alguien le sirve, uso el tipo de archivo "texto para Mac" dado que es el unico que no agrega un retorno de linea al final del archivo, ni para las filas vacias de la hoja que se exporta.

    Si alguien sabe como solucionar esto, le agradeceria la ayuda

    ResponderEliminar
  2. Se me ocurre que, aun no sabiendo el motivo de este error, podria solucionarlo si pudiera desbloquearlo antes de grabar y luego bloquear nuevamente la visualizacion el proyecto, todo esto desde el codigo de VB.
    O enviar una secuencia de teclas para desbloquear antes de usar SaveAs (Alt+F11, Alt+h, p, "contraseña", Enter).

    Pero no se como hacer ninguna de estas dos.

    ResponderEliminar
    Respuestas
    1. Hola,
      yo probaría primero quitando la opción de Private.

      Slds

      Eliminar
  3. La opcion Private la agregue despues, tambien falla sin eso.

    Logre resolverlo temporalmente usando la sentencia SendKeys para entrar al editor de VB, poner la clave para ver el proyecto y salir del editor. A partir de ahi el resto del codigo funciona bien. Al terminar la macro se ciera el archivo, de modo que igual nadie puede ver el proyecto.

    Private Function grabar()
    ThisWorkbook.Save
    SendKeys "%{F11}%(HP)xxxxxxxx{ENTER}{ESC}{ESC}%(AC)", -1
    Sheets("archivo").Visible = True
    Sheets("archivo").Select
    ThisWorkbook.SaveAs Filename:= _
    ThisWorkbook.Path & "\bna1.txt", FileFormat:=19, _
    CreateBackup:=0
    ThisWorkbook.Close SaveChanges:=0
    End Function

    donde xxxxxxxx es la contraseña de proteccion del provecto de VB.

    Igual sigo intrigado por el origen de esta falla, me gustaria descubrirla en lugar de usar estos "parches".

    ResponderEliminar