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

Question

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?

aquarelle · Answer

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

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

1 response

Similar discussions

Subscribe To Our Newsletter!