Previous Topic Next Topic

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

Closed
Pluggie
Posts
13
Registration date
Monday March 11, 2013
Status
Member
Last seen
August 14, 2013
 - Apr 12, 2013 at 09:17 AM
aquarelle
Posts
7103
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
May 21, 2022
 - Apr 12, 2013 at 03:10 PM
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

Answer 1 / 1
aquarelle
Posts
7103
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
May 21, 2022
 490
Apr 12, 2013 at 03:10 PM
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."
0