jCombineSmart()
Estimated reading time: 4 minutesFunction Summary
The jCombineSmart() function helps to concatenate the values of cells while simultaneously skipping any empty cells and removing any duplicates. jCombineSmart() can use both a cell range and a list of cell addresses. When using a list of cell addresses, they must be enclosed in parenthesis and delimited by commas, e.g. (A1:A5,B1:B20). When a continuous sequence of numbers is found in the selected range, only the first an last numbers in the sequence will be shown, and the middle values will be truncated to two periods, e.g. (1,2,3,4,5) becomes (1..5). This range code is set by default but may be customized by develpers. Converting to numeric is also a feature available for developers to trim any leading zeros from the selected range. This feature is false by default.
Function Arguments
| Argument Name | Description | Default | Optional |
|---|---|---|---|
| Selected Range | Designates a range of cells, within a worksheet, to concatenate. This can also be used with a delimited list of cells. | NO | |
| Delimiter | Defines the character value to designate a separation between cell values. A comma is the default delimiter. | ”,” | YES |
| Range Code | Defines the characters to designate when a list of cells is concatenated into a range. | ”..” | YES |
| Convert to Numeric | This will remove any leading zeros within the Selected Range. Executing the jCombineSmart() function wil will reflect the results of this feature. | FALSE | YES |
Excel Formula Bar Example
jCombineSmart(A15:A30,,,TRUE)
This is a simplified example to show the format you would see in Excel.
Example Function Composition
| Argument Name | Example Mapping | Explanation |
|---|---|---|
| Function Name | =jCombineSmart() | The Excel function name used to call the function. It can be used alone in a report and can be embedded inside of Data or Formatting functions |
| Selected Range | (A15:A30) | In this example a range of values would look like this: ValueOfA15 through ValueOfA30 |
| Delimiter | ”” | Leaving the delimiting value blank means that jCombineSmart will use its default comma delimiter. Set a delimiting character value by entering a character into the function after the range value. For example, “;”. |
| Range Code | ”” | Like the delimiter, keeping this blank means jCombineSmart will use its default value of two periods. Change it by adding a charactor or series of charactors of your choosing. |
| Convert To Numeric | TRUE | Any leading zero will now be trimmed off. For example 0001 will trim to 1. |