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:
El resultado, como se vé en la imagen siguiente, rompe cualquier limitación anterior... permitiendo además elegir el separador a incluir:
Una ayuda para los que no trabajamos con Excel 365...
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 FunctionEl resultado, como se vé en la imagen siguiente, rompe cualquier limitación anterior... permitiendo además elegir el separador a incluir:
Una ayuda para los que no trabajamos con Excel 365...
Gracias por el foro y la ayuda.
ResponderEliminarLegue 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
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.
ResponderEliminarO 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.
Hola,
Eliminaryo probaría primero quitando la opción de Private.
Slds
La opcion Private la agregue despues, tambien falla sin eso.
ResponderEliminarLogre 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".