It will concatenate serveral numbers into one cell separated by a semicolon.
The content of the cells mentioned in the string are references to other cells.
Sometimes the content of the cells in the string = 0, because the referenced cell is empty.
When this happens I would like to exclude the cell from the string result.
I know I could add if-statements to the string, but that would get really messy and I'm not sure it can handle more than 7 consecutive statements in Excel 2007 already???
So I'm looking for a cleaner way to do this.
Do you have some knowledge about VBA macro?
Because with VBA it is possible to create a custom function.
First, press alt+F11 to open VBA editor and insert a module. Paste this custom function into this module :
Function ConcatPlageNotEmptyCel(plage As range, Optional separator As String = "; ") As String
Dim rep As String, c As range
For Each c In plage
If c.Value <> "" Then
rep = rep & c.Value & separator
End If
Next c
ConcatPlageNotEmptyCel = Left(rep, Len(rep) - Len(separator))
End Function
To use it on your sheet ---> insert / function / custom and select ConcatPlageNotEmptyCel.
This function expects two parameters:
- range : the range of cells you want to concatenate (selected with the mouse)
- separator : the character separating each cell (1 or more characters) but if the separator is not specified, the default is defined by ";"
Best regards
"Pour trouver une solution à ses problèmes, il faut s'en donner la peine."
