ajRegExpMatch function
Description
The ajRegExpMatch function searches an input string for a substring that matches the regular expression and return the matched result.
Syntax
ajRegExpMatch(input_string, pattern, [start_position], [ignore_case], [multi_line], [global_match])
| Argument Name | Argument Type | Description |
|---|---|---|
| input_string (required) | Range / Array | The range of cells to be set as the message to be searched. The range of cells can span across multiple rows and columns and it will be concatenated into a single string. It will concatenate the row first follow by the column. For example, Cell A1, “Hello” Cell A2, “You” Cell B1, “I” Cell B2, “Good” Concatenated result => “HelloIYouGood” |
| pattern (required) | String | The regular expression to match. It follows the Basic Regular Expressions (BRE) of the IEEE POSIX standard. |
| start_position (optional) | Double | The starting search position of ‘Input_string’. 1 is the first position. It will begin from the first position by default. |
| ignore_case (optional) | Boolean | TRUE for case insensitive search. FALSE for case sensitive search. The default value is FALSE. |
| multi_line (optional) | Boolean | If it equals TRUE, and ‘Input_string’ has new line or carriage return characters, the ^ and $ operators will match against a new line boundary. If it equals FALSE, and ‘Input_string’ has new line or carriage return characters, the ^ and $ operators will match against a string boundary. The default value is FALSE. |
| global_match (optional) | Boolean | If it equals TRUE, the function will return all match cases occurrence. If it equals FALSE, the function will return the first match case occurrence only. If the length of the result is larger than the cell limitation length 32767, it will split the match result and return it in a row array. If more than 1 occurrence is found, it will return the matches in a column array. The default value is FALSE. |
The function will return:
1) Return Value: Matched regular expression result
2) Return Type: Single Value / Multiple values (array formula)
Examples
Here are a few examples of the ajRegExpMatch function.
Example 1 (basic)
The function below is given the required parameters and except ‘Ignore_case’ is TRUE. The rest of the optional parameters will use the default value.
Since we are expecting the length of output string to be short (because ‘Input_string’ came from 1 cell and the ‘Pattern’ does not use any repetition operators or metacharacters), we assign the function to a cell and the match result is returned within the same cell.
Example 2 (negative case for the global match)
In this example, the ‘Input_string’ is appended with additional string characters and it is spanned into 2 columns and 4 rows. Do take note the ‘Patten’ has changed and ‘Global_match’ equals ‘FALSE’.
For this example, we are assigning the function to a cell array with 3 rows and 2 columns. 2 columns are assigned because we are expecting the length of output string to be long (because ‘Input_string’ is defined with more than one cell and the ‘Pattern’ uses metacharacters). 3 rows are assigned because in a global match result, it is possible to have more than 1 match case and the result will be returned into the row cell array.
Since ‘Global_match’ equals ‘FALSE’, the function will return the first occurrence only, in which the first “will” will be returned given the ‘Pattern’ and the match result is returned into the first cell array, B24:C24.
Example 3 (positive case for the global match)
In this example, the same ‘Input_string’ and parameters are used as like Example 2 except ‘Global_match’ equals ‘TRUE.
Similarly, we are assigning the function to a cell array with 3 rows and 2 columns. 2 columns are assigned because we are expecting the length of the output string to be long (because ‘Input_string’ is defined with more than one cell and the ‘Pattern’ uses metacharacters). 3 rows are assigned because in a global match result, it is possible to have more than 1 match case and the result will be returned into the row cell array.
Since ‘Global_match’ equals ‘TRUE, the function will return all occurrences. Therefore, based on the ‘Pattern’ it will return 2 sets of match cases. The match result is returned into the first cell array, B41:C41 and the second cell array, B42:C42.
Example 4 (multi-line case negative)
In this example, the ‘Input_string’ has changed slightly with a new line. Do take note the ‘Pattern’ has changed and ‘Multi_line’ equals ‘FALSE’.
Similarly, we are assigning the function to a cell array with 3 rows and 2 columns. 2 columns are assigned because we are expecting the length of the output string to be long (because ‘Input_string’ is defined with more than one cell and the ‘Pattern’ uses metacharacters). 3 rows are assigned because in a global match result, it is possible to have more than 1 match case and the result will be returned into the row cell array.
Since ‘Multi_line’ equals ‘FALSE’, the function will not be able to match any case. It is because the string will contain a new line, “\n” in between the start (^) and end ($) of the string. Therefore, the ‘Pattern’ does not match any new line “\n”. Thus an empty string is returned.
Example 5 (multi_line case positive)
In this example, the same ‘Input_string’ and parameters are used as like Example 2 except ‘Multi_line’ equals ‘TRUE.
Similarly, we are assigning the function to a cell array with 3 rows and 2 columns. 2 columns are assigned because we are expecting the length of output string to be long (because ‘Input_string’ is defined with more than one cell and the ‘Pattern’ uses metacharacters). 3 rows are assigned because in a global match result, it is possible to have more than 1 match case and the result will be returned into the row cell array.
Since ‘Multi_line’ equals ‘TRUE’, the function will be able to match the ‘Input_string’ to the ‘Pattern’. It is because the string will split into 2 strings by the new line, “\n”. Therefore, the ‘Pattern’ is able to match and the results are returned to the first and the second elements of the cell array.
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.