Exclude "0" values from string: =H2&";"&I2&"
Closed
Pluggie
aquarelle
- Posts
- 13
- Registration date
- Monday March 11, 2013
- Status
- Member
- Last seen
- August 14, 2013
aquarelle
- Posts
- 7103
- Registration date
- Saturday April 7, 2007
- Status
- Moderator
- Last seen
- May 21, 2022
Related:
- Exclude "0" values from string: =H2&";"&I2&"
- Can the formula COUNTA / COUNT exclude 0 values? ✓ - Forum - Excel
- Parse error: syntax error, unexpected single-quoted string - Guide
- 192 l.168.0.1 - Guide
- Pokemon unbound 2.0 download - Download
- Unable to access http://192.168.0.1 - Guide
1 reply
aquarelle
Apr 12, 2013 at 03:10 PM
- Posts
- 7103
- Registration date
- Saturday April 7, 2007
- Status
- Moderator
- Last seen
- May 21, 2022
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 :
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."
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."