Exclude "0" values from string: =H2&";"&I2&&quot

[Closed]
Report
Posts
13
Registration date
Monday March 11, 2013
Status
Member
Last seen
August 14, 2013
-
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
-
Hello,

I'm using the following string in excel.
=H2&";"&I2&";"&J2&";"&K2&";"&L2&";"&M2&";"&N2&";"&O2&";"&P2&";"&Q2

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.

Anybody have an idea?

1 reply

Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
488
Hello,

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."