SubstituteArray
The SubstituteArray() function is used to replace values in a data range with a specified value. The values to be replaced are supplied in a separate list.
Related functions: ArrayToString, StringToArray, TransposeArray, Null
Syntax:
Qx.SubstituteArray(Range, [Replacement Value], [Value List])- In Excel with Quintessence add-in versions prior to 25.1, use:
SubstituteArray() - In the Quintessence Editor (Studio) using Quintessence language, use:
SubstituteArray() - In the Quintessence Editor (Studio) using Python language, use:
Qx.SubstituteArray()
ⓘ See parameter descriptions
| Range | The string to be split. |
| Replacement Value (optional) | The value that will replace matches found in the input range. |
| Value List (optional) | The list of values to be replaced. This can be a range, an array, or explicit values. |
Remarks:
- The default parameters are:
- Replacement Value: Empty string
- Value List: An array of all Excel error values (e.g.,
#NAME?,#DIV/0!,#NULL!, etc.)
Examples:
The examples provided below are based on fictitious data for illustrative purposes only.
> Example 1: Replacing specified values with another value
Range (A1:B5):
| Task 1 | 10% |
| Task 2 | 99% |
| Task 3 | 100% |
| Task 4 | 45% |
| Task 5 | 85% |
Values to replace (D1:D3):
| 98% |
| 99% |
| 100% |
Replace all percentages ≥ 98% with Completed. Function call:
=Qx.SubstituteArray(A1:B5, "Completed", D1:D3) Result:
| Task 1 | 10% |
| Task 2 | Completed |
| Task 3 | Completed |
| Task 4 | 45% |
| Task 5 | 85% |
> Example 2: Replacing Excel errors with an empty string
Range (A1:D4):
| ABS | 100% | FALSE | 2020 |
| #NAME? | #DIV/0! | TRUE | #NUM! |
| ACOS | 30% | TRUE | 2020 |
| #N/A | #DIV/0! | #NULL! | 2000 |
Using default parameters removes error values. Function call:
=Qx.SubstituteArray(A1:D4)Result:
| ABS | 100% | FALSE | 2020 |
| TRUE | |||
| ACOS | 30% | TRUE | 2020 |
| 2000 |