PrecisionCalc
inspector text
Do Anything with Text in Formulas

 

itREGEX

Performs a Regular Expressions search. Can return the value, position, or length of the first match, or an array of the values, positions, or lengths of all matches, or a count of matches. Can replace text. Can be case sensitive or not.

Regular Expressions syntax is very complex, and explaining it is beyond the scope of this helpfile and of inspector text. You may wish to search the web for "Regular Expressions" and "Syntax" for more information. Books have also been published on Regular Expressions for beginners.

If you are not familiar with Regular Expressions syntax, use itSEARCH, itREPLACE, and other inspector text functions instead of itREGEX.

 

To return multiple values when a return_type of 5 - 7 is specified, itREGEX must be array-entered. To array-enter an itREGEX formula, follow these steps:

  1. Select the cells in which you want the array of values. The selection should be either one column wide (and down as many rows as desired), or one row tall (and across as many columns as desired).
  2. With the cells selected, type the formula in the top-left cell of the selected cells. Do not press Enter yet!
  3. With the cells still selected, and with the top-left cell still in Edit Mode (because you haven't pressed Enter yet), press and hold down the CTRL and SHIFT keys.
  4. With the cells still selected, and with the CTRL and SHIFT keys still held down, press Enter.
  5. You should see the table in the selected cells.

 

 

Syntax

itREGEX(string_to_search, regex, case_sensitive, replace_text, return_type, not_found_return)

string_to_search Required. The string in which to search for the regex.
regex Required. The Regular Expressions pattern with which to search string_to_search.
case_sensitive Optional. Determines whether the search distinguishes between lower case and upper case. Set to False to ignore case differences. True by default.
replace_text Optional. Text with which to replace regex. If replace_text is omitted, no replacement is done.

If replace_text is specified, the return value is the complete, modified string_to_search after all replacements have been made.

By default, replace_text replaces only the first match found in string_to_search. To replace all matches, set return_type to -1.

If replace_text is an empty string (""), itREGEX checks return_type to determine whether to replace text. If return_type is 0, -1, or omitted, it replaces text. If return_type is anything else, no replacement is done.
return_type

Optional. Determines what kind of information is returned about the match or matches. 1 by default.

If replace_text is specified (even if just as an empty string), the only valid return_types are 0 and -1, and all other return_types are ignored.

return_type value

 Result
1 Position number from the left, of the first match. Default if return_type is not specified.
2 Length of the first match.
3 Value of the first match.
4 Count of matches.
5 Array of positions, from the left, of all matches. Must be array-entered.
6 Array of lengths of all matches. Must be array-entered.
7 Array of values of all matches. Must be array-entered.
0 If replace_text is specified, replaces only the first match. Default if return_type is specified. Ignored if return_type is not specified.
-1 If replace_text is specified, replaces all matches. Ignored if return_type is not specified.
Omitted If return_type is omitted, and replace_text is also omitted, then return_type defaults to 1.

If return_type is omitted, and replace_text is specified, even if replace_text is specified as an empty string (""), return_type defaults to 0, and a replacement is made using replace_text.
not_found_return Optional. Text or value returned if no match is found. #VALUE! by default.

 

Remarks

 

Examples
These are very simple examples not intended to illustrate Regular Expressions syntax. They are only intended to illustrate itREGEX syntax.

Formula Description Result
=itREGEX("abcde", "b") Find b in abcde, and return the position number, counting from the left. 2
=itREGEX("abcde", "B", FALSE) Find B or b in abcde, and return the position number, counting from the left. 2
=itREGEX("abcde", "b", , "X") Find b in abcde, replace it with X, and return the modified string. aXcde
=itREGEX("abcbebf", "b", , , 5) Find b in abcbebf, and return an array of position numbers, counting from the left, of all matches.

Array-entered in A1:C1:

  A B C
1 2 4 6
=itREGEX("abcbebf", "b", , "") Find b in abcde, replace the first match with an empty string (""), and return the modified string. acbebf
=itREGEX("abcbebf", "b", , "", -1) Find b in abcde, replace all matches with an empty string (""), and return the modified string. acef
=itREGEX("abcde", "X", , , , "Not Found") Find X in abcde, and if not found return "Not Found" instead of the default "#VALUE!" Not Found

 

 

PrecisionCalc Home Page