SubstituteArray
The SubstituteArray() function is used to replace values in a range of data with a specified value. The values to be replaced are provided in a separate list.
Related functions: ArrayToString, StringToArray, TransposeArray, Null
Syntax
SubstituteArray (Range, Replacement Value, Value List)
| Range | The range of cells in Excel used as input |
| Replacement Value | The value that will replace specified values in the input. The default is an empty string. |
| Value List | The specified values to replace. The default is an array of all Excel errors. |
Note:
Input parameters can consist of Quintessence functions. This includes functions that return ranges in cases where a parameter value can be a range.
Examples
Example 1. Replacing specified values with another value
Example 2. Replacing Excel errors with an empty string
Example 3. Replacing empty values using Null()
Note:
How your system administrator configured Quintessence determines which function parameters are required. In most implementations, administrators configure functions so that certain parameters default to specific values when left blank.
Example 1. Replacing specified values with another value
The percentages specified in the fourth column (Values to replace) are replaced with the word ‘Completed’ (Replacement value) in the input range.
| Range | Replacement value | Values to replace | Substitution | |
| Task 1 | 10% | Completed | 98% | =SubstituteArray(A2:B10,C2,D2:D4) |
| Task 2 | 99% | 99% | ||
| Task 3 | 100% | 100% | ||
| Task 4 | 45% | |||
| Task 5 | 85% | |||
| Task 6 | 98% | |||
| Task 7 | 77% | |||
| Task 8 | 100% | |||
| Task 9 | 92% | |||
| Range | Replacement value | Values to replace | Output | ||
| Task 1 | 10% | Completed | 98% | Task 1 | 10% |
| Task 2 | 99% | 99% | Task 2 | Completed | |
| Task 3 | 100% | 100% | Task 3 | Completed | |
| Task 4 | 45% | Task 4 | 45% | ||
| Task 5 | 85% | Task 5 | 85% | ||
| Task 6 | 98% | Task 6 | Completed | ||
| Task 7 | 77% | Task 7 | 77% | ||
| Task 8 | 100% | Task 8 | Completed | ||
| Task 9 | 92% | Task 9 | 92% | ||
Example 2. Replacing Excel errors with an empty string
The SubstituteArray() function is used to replace all Excel errors in the input range with an empty string. ‘ValueList’ is omitted, so the default Excel error list is used instead.
| Range | Substitution | |||
| ABS | 10 | FALSE | 20200 | =SubstituteArray(A2:D9) |
| #NAME? | #DIV/0! | #NULL! | #NUM! | |
| ACOS | 30 | TRUE | 20200 | |
| #N/A | #DIV/0! | #NULL! | #NUM! | |
| ASIN | 50 | TRUE | 20200 | |
| ATAN | 60 | TRUE | 20200 | |
| #VALUE! | #NUM! | #NULL! | #NULL! | |
| #REF! | #REF! | #REF! | #REF! | |
| Range | Output | ||||||
| ABS | 10 | FALSE | 20200 | ABS | 10 | FALSE | 20200 |
| #NAME? | #DIV/0! | #NULL! | #NUM! | ||||
| ACOS | 30 | TRUE | 20200 | ACOS | 30 | TRUE | 20200 |
| #N/A | #DIV/0! | #NULL! | #NUM! | ||||
| ASIN | 50 | TRUE | 20200 | ASIN | 50 | TRUE | 20200 |
| ATAN | 60 | TRUE | 20200 | ATAN | 60 | TRUE | 20200 |
| #VALUE! | #NUM! | #NULL! | #NULL! | ||||
| #REF! | #REF! | #REF! | #REF! | ||||
Example 3. Replacing empty values using Null()
The SubstituteArray() function is used to replace empty cells with the word ‘Weekend’. The first function looks for empty strings (“”). Because the blank cells have no value (and therefore are not empty strings), the function does not work. The second function looks for null values when making replacements. This function works as it recognises the blank cells as being empty.
| AGL | Close Price | Close Price – substitute “” | Close Price – substitute Null values | Replacement Value |
| 01 April 2015 | 25604 | =SubstituteArray(B2:B9,E2,””) | =SubstituteArray(B2:B9,E2,Null()) | Weekend |
| 02 April 2015 | 24970 | |||
| 03 April 2015 | 25369 | |||
| 04 April 2015 | ||||
| 05 April 2015 | ||||
| 06 April 2015 | 24684 | |||
| 07 April 2015 | 25402 | |||
| 08 April 2015 | 25639 |
| AGL | Close Price | Incorrect output | Correct output | Replacement Value |
| 01 April 2015 | 25604 | 25604 | 25604 | Weekend |
| 02 April 2015 | 24970 | 24970 | 24970 | |
| 03 April 2015 | 25369 | 25369 | 25369 | |
| 04 April 2015 | Weekend | |||
| 05 April 2015 | Weekend | |||
| 06 April 2015 | 24684 | 24684 | 24684 | |
| 07 April 2015 | 25402 | 25402 | 25402 | |
| 08 April 2015 | 25639 | 25639 | 25639 |