ajBigStrSubstitute function
Available for AlchemyJ Excel Library, AlchemyJ Enterprise Edition
Description
Replaces existing text with new text in a Big String.
Syntax
ajBigStrSubstitute(memory_id, old_memory_id, new_memory_id, instance_num, [run_condition], [run_by_function_point_only])
Argument Name | Argument Type | Description |
---|---|---|
memory_id (big string ID) (required) | String | The text or ID of the big text for which you want to substitute the characters. A big string ID can be retrieved from ajBigStrSet() or configure Big String value from More Tools tab in AlchemyJ ribbon. |
old_memory_id (required) | String | It is the big string ID of the existing text you want to replace. |
new_memory_id (required) | String | It is the big string ID of the next text you want to replace existing text with. |
instance_num (optional) | Long | Specifies which occurrence of existing text you want to replace. |
run_condition (optional) | Boolean | The function will run when the value is TRUE. Otherwise will not run. The default value is TRUE. |
run_by_function_point_only (optional) | Boolean | If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only. The default value is TRUE. |
The function will return:
1) Return Value: Big String
2) Return Type: Single Value
Example
In this example, we will demonstrate how to substitute part of a Big String text with a different Big String text. In this example, we would be using the word 'thisisalonglongstring' and transform it to the word 'thisisashortlongstring'. This is done by replacing the first instance of 'long' with 'short'.
The original Big String value we have used here is 'thisisalonglongstring'. To use the ajBigStrReplace formula, firstly we have to convert 'thisisalonglongstring', 'long' and 'short' respectively into a Big String old big string ID. This is done using the ajBigStrSet formula. 'long' would be the old_memory_id and 'short' would be the new_memory_id.
We can then use the formula as shown in B15 and it will return a Big String value.
To verify the result, we can use the ajBigStrValue formula in B17 to make it into a normal String. As seen in B18, we have successfully transformed the word from 'thisisalonglongstring' to 'thisisashortlongstring' by replacing part of the Big String.
Click here to download the use case workbooks for further reference.
Error Scenarios
It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will also raise error for below scenario(s).
Error Scenario |
---|
Big String ID does not exist. |
Old big string ID does not exist. |
New Big String ID does not exist. |
Instance number should be greater than zero. |