PrecisionCalc
inspector text
Do Anything with Text in Formulas

 

itSEARCH

Allows extremely versatile and flexible text searching. Locates one or more text strings within another text string. Can return any of 22 kinds of information about the text found, such as position, position from right, text string found, count of all text strings found, array of all text strings found, etc. (see the return_type argument). Can be case sensitive or not. Can search from left or from right.

 

To return multiple values when a return_type of 10 - 13 or 15 - 19 is specified, itSEARCH must be array-entered. To array-enter an itSEARCH 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

itSEARCH(string_to_search,find_text,case_sensitive,return_type,find_before_text,find_not_before_text,return_position_offset,return_length_offset,search_from_right,not_found_return)

string_to_search Required. The text string in which to search for find_text.
find_text Required. The text to search for within string_to_search.

The following characters must be preceded by a backslash to find them as literal characters because otherwise they have special meaning in find_text:

[ \ | ^ ? * ( )

For example, to find a question mark, use this:

\?

To find a backslash character, use this:

\\

 

Wildcards and other Substitutions

 Description Example
? Finds any single character. b?t finds bat, bet, bit, bxt, b5t, b-t, etc.
* Finds any number of any characters, including no character. b*t finds bt, bat, bet, bit, brat, babct, b123t, b-@#&t, etc.
|
(pipe character)		 Finds either the text before it or the text after it. Can be used multiple times to increase the number of different possible finds.

Can be enclosed within parentheses to restrict its effect to text within the parentheses.

Other wildcards and substitutions can be used within this.

Alternative Syntax:
If all possibilities are single characters, they can be enclosed within square brackets instead of separated by pipe characters.

 a|b finds a or b.

aa|bb|cc|dd finds aa, or bb, or cc, or dd.

b(a|e)t finds bat or bet.

a*|?b finds "a" with any number of characters (or no character) after it, OR finds "b" with exactly one character before it.

Alternative Syntax:
[abcd] finds a, or b, or c, or d.
[range] Finds text or digits within the specified range. Beginning and end of range are separated by a hyphen. [a-d] finds a, or b, or c, or d.

[3-5] finds 3, or 4, or 5.
{repetitions}

{min, max}

{min,}

 Finds the text immediately prior to it in find_text a given number of repetitions, or within a specified range of minimum and maximum repetitions.

If maximum is omitted (but a comma is included after minimum), the maximum is infinite.

 a{3} finds aaa.

a{2,4} finds aa, or aaa, or aaaa.

ab{2,4} finds abb, or abbb, or abbbb.

(ab){2,4} finds abab, or ababab, or abababab.

(a|b){2} finds aa, or ab, or ba, or bb.

a{2,} finds two or more a's (maximum is infinite).
[^not] Finds any character other than the character or range specified. [^a] finds any character other than "a".

a[^b] finds "a" followed by any character other than b.

a[^b-d] finds "a" followed by any character other than b, c, or d.
case_sensitive Optional. Determines whether the search distinguishes between lower case and upper case. Set to False to ignore case differences. True by default.
return_type Optional. Determines what kind of information is returned about the text found. 1 by default.

 

return_type value

Result
TRUE Returns True if one or more strings are found. If no strings are found, returns not_found_return, or returns False if not_found_return is omitted.

Same as return_type 21, but easier to remember.
1 Position number from the left, of the first string found.

This is the default if return_type is not specified.
2 Position number from the right, of the first string found. If the string found is more than one character long, this is the position of the rightmost character.
3 First string found.
4 Length of the first string found.
5 Cell contents to the left of the first string found.
6 Cell contents to the right of the first string found.
7 Cell contents to the left of the first string found, plus the first string found.
8 First string found, plus the cell contents to the right of the first string found.
9 Count of all strings found.
10 Count of all sets of unfound cell contents.
11 Array of position numbers, counting from the left, of all strings found. Must be array-entered.
12 Array of position numbers, counting from the right, of all strings found. For strings more than one character long, this is the position of the rightmost character. Must be array-entered.
13 Array of all strings found. Must be array-entered.
14 Array of the lengths of all strings found. Must be array-entered.
15 Total length of all strings found.
16 Array of sets of unfound cell contents to the left of each string found. Must be array-entered.
17 Array of sets of unfound cell contents to the right of each string found. Must be array-entered.
18 Array of all sets of unfound cell contents. Must be array-entered.
19 Array of unfound cell contents to the left of each string found, plus each string found. Must be array-entered.
20 Array of each string found, plus unfound cell contents to the right of each string found. Must be array-entered.
21 Returns True if one or more strings are found. If no strings are found, returns not_found_return, or returns False if not_found_return is omitted.
99 Returns the regex ("regular expression") used by itSEARCH. Can be used in itREGEX. May be useful for learning to use regular expressions.

string_to_search, case_sensitive, return_type, return_position_offset, return_length_offset, search_from_right, and no_match_return do not affect the regex.

Regex syntax is very complex, and explaining it is beyond the scope of this helpfile and 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.
find_before_text Optional. Restricts search to text to the left of find_before_text (or to the right, if search_from_right is True). Empty by default.

Can use all the same wildcards and substitutions as find_text.

The following characters must be preceded by a backslash to use them as literal characters because otherwise they have special meaning:

[ \ | ^ ? * ( )
find_not_before_text Optional. Restricts search to text that is not to the left of find_not_before_text (or to the right, if search_from_right is True). Empty by default.

Can use all the same wildcards and substitutions as find_text.

The following characters must be preceded by a backslash to use them as literal characters because otherwise they have special meaning:

[ \ | ^ ? * ( )
return_position_offset Optional. Offsets the position of the return value or values. Positive numbers offset the result to the right; negative numbers offset the result to the left. 0 by default.
return_length_offset Optional. Offsets the length of the return value or values. Positive numbers make the result longer; negative numbers make the result shorter. 0 by default.
search_from_right Optional. If True, searches from right to left instead of from left to right. False by default.

Note that search_from_right changes the effect of certain return types:

  • Position numbers counting from the left become position numbers counting from the right, and vice versa.
  • Cell contents to the right of the string found become cell contents to the left of the string found, and vice versa.

If searching from the right for more than one character, find_text must be reversed.

If search_from_right is True, itSEARCH simply reverses string_to_search, then proceeds as usual.
not_found_return Optional. Text or value returned if find_text is not found. #VALUE! by default.

 

Remarks

 

Examples

Formula Description Result
=itSEARCH("abcde", "b") Find b, and returns its position counting from the left. 2
=itSEARCH(A1, B1) Find the contents of cell B1 within the contents of cell A1. Depends on the contents of cells A1 and B1.

Wildcard and Substitution Examples:
=itSEARCH("abcde", "?d") Find d, and one character before d, and return its position counting from the left. 3
=itSEARCH("abcde", "?d", , 3) Find d, and one character before d, and return the first string found. cd
=itSEARCH("abcde", "*d", , 3) Find d, and any number of characters (including no character) before d, and return the first string found. abcd
=itSEARCH("better bat", "b(e|a)t", , 3) Find b, followed by either e or a, then followed by t, in "better bat", and return the first string found. bet
=itSEARCH("bitter bat", "b(e|a)t", , 3) Find b, followed by either e or a, then followed by t, in "bitter bat", and return the first string found. bat
=itSEARCH("abcdefg", "[b-e]", , 3) Find b, or c, or d, or e, and return the first one found. b
=itSEARCH("babbbcbbbbb", "b{2,}", , 3) Find 2 or more b's in a row, and return the first string found. bbb
=itSEARCH("babbbbbc", "b{2,4}", , 3) Find 2 - 4 b's in a row, and return the first string found. bbbb
=itSEARCH("abcde", "b[^d]", , 3) Find b followed by any character other than d, and return the first string found. bc
=itSEARCH("bcbdbe", "b[^b-d]", , 3) Find b followed by any one character other than b, c, or d, and return the first string found. be
=itSEARCH("!@?#$%^?&", "\?") Find a question mark character, and return the position of the first one found. 3
The question mark is one of the characters that must be preceded by a backslash to treat it as a literal character. See note above.
=itSEARCH("xabqzcnum2xyz", "?b*c(m?n[1-3]|n?m[1-3])[^a-c]", , 3) This example shows one way of combining various wildcards and substitutions. abqzcnum2x
=itSEARCH("qacbdgxyz", "a[^b][^c](d|e)(f|g)??", , 3) This example shows one way of repeating wildcards and substitutions. acbdgxy

case_sensitive Examples:
=itSEARCH("abcde", "B") Find B (not b), and returns its position counting from the left. #VALUE!
=itSEARCH("abcde", "B", FALSE) Find B or b, and returns its position counting from the left. 2

return_type Examples:
=itSEARCH("abcde", "b", , 2) Find b, and return its position counting from the right. 4
=itSEARCH("abcdefg", "b*e", , 4) Find b, followed by any number of any characters, then e, and return the length of the first string found. 4
=itSEARCH("abcde", "b", , 5) Find b, and return the cell contents to the left of the first string found. a
=itSEARCH("abcde", "b", , 6) Find b, and return the cell contents to the right of the first string found. cde
=itSEARCH("abcde", "b", , 7) Find b, and return the cell contents to the left of the first string found, plus the first string found. ab
=itSEARCH("abcde", "b", , 8) Find b, and return the first string found, plus the cell contents to the right of the first string found. bcde
=itSEARCH("bxbyb", "b?", , 9) Find b followed by any one character, and return a count of the number of strings found. 2
=itSEARCH("babcbe", "b", , 10)
(array-entered in A1:A3, or A1:C1)		 Find all occurrences of b, and return an array of position numbers, counting from the left.

Must be array-entered.

Array-entered in A1:A3:
 
  A
1 1
2 3
3 5

Array-entered in A1:C1:

  A B C
1 1 3 5
=itSEARCH("babcbe", "b", , 11)
(array-entered in A1:A3, or A1:C1)		 Find all occurrences of b, and return an array of position numbers, counting from the right.

Must be array-entered.

Array-entered in A1:A3:
 
  A
1 2
2 4
3 6

Array-entered in A1:C1:

  A B C
1 2 4 6
=itSEARCH("bacbefbghbij", "?b", , 12)
(array-entered in A1:A3, or A1:C1)		 Find all occurrences of b, and exactly one character before b, and return an array of all strings found.

Must be array-entered.

Array-entered in A1:A3:
 
  A
1 cb
2 fb
3 gb

Array-entered in A1:C1:

  A B C
1 cb fb gb
=itSEARCH("bcbacbaac", "b*c", , 13)
(array-entered in A1:A3, or A1:C1)		 Find b, followed by any number of any characters, then c, and return an array of the lengths of all strings found.

Must be array-entered.

Array-entered in A1:A3:
 
  A
1 2
2 3
3 4

Array-entered in A1:C1:

  A B C
1 2 3 4
=itSEARCH("abcabacabaaca", "b*c", , 14) Find b, followed by any number of any characters, then c, and return the total length of all strings found. 9
(2 + 3 + 4 = 9)
     (bc length = 2)
     (bac length = 3)
     (baac length = 4)
=itSEARCH("abacbcedbc", "b", , 15)
(array-entered in A1:A3, or A1:C1)		 Find b, and return an array of sets of unfound cell contents to the left of each string found.

Must be array-entered.

Array-entered in A1:A3:
 
  A
1 a
2 ac
3 ced

Array-entered in A1:C1:

  A B C
1 a ac ced
=itSEARCH("abacbcedbc", "b", , 16)
(array-entered in A1:A3, or A1:C1)		 Find b, and return an array of sets of unfound cell contents to the right of each string found.

Must be array-entered.

Array-entered in A1:A3:
 
  A
1 ac
2 ced
3 c

Array-entered in A1:C1:

  A B C
1 ac ced c
=itSEARCH("abacbcedbc", "b", , 17)
(array-entered in A1:A3, or A1:C1)		 Find b, and return an array of all sets of unfound cell contents.

Must be array-entered.

Array-entered in A1:A3:
 
  A
1 a
2 ac
3 ced
4 c

Array-entered in A1:C1:

  A B C D
1 a ac ced c
=itSEARCH("abacbcedbc", "b", , 18)
(array-entered in A1:A3, or A1:C1)		 Find b, and return an array of unfound cell contents to the left of each string found, plus each string found.

Must be array-entered.

Array-entered in A1:A3:
 
  A
1 ab
2 acb
3 cedb

Array-entered in A1:C1:

  A B C
1 ab acb cedb
=itSEARCH("abacbcedbc", "b", , 19)
(array-entered in A1:A3, or A1:C1)		 Find b, and return an array of each string found, plus unfound cell contents to the right of each string found.

Must be array-entered.

Array-entered in A1:A3:
 
  A
1 bac
2 bced
3 bc

Array-entered in A1:C1:

  A B C
1 bac bced bc
=itSEARCH("ababa", "b", , 21) Find b, and return True if one or more strings are found. If no strings are found,  return False. TRUE
=itSEARCH("ababa", "c", , 21) Find c, and return True if one or more strings are found. If no strings are found,  return False. FALSE
=itSEARCH("abcdefgh", "c?e*f", , 99, "g*h", "?x") Find c, followed by any one character, followed by e, followed by any number of any characters (including no character), followed by f. The string found must appear before g followed by any number of any characters followed by h, and must not appear before any one character followed by x. Return the Regex. c.e.*?f(?=g.*?h)(?!.x)

find_before_text Examples:
=itSEARCH("abcdefgh", "b*c", , 3, "d*g") Find b, followed by any number of any characters (including no character), followed by c. The string found must appear immediately before d followed by any number of any characters (including no character), followed by g. Return the string found. bc
=itSEARCH("abcdefgh", "b*c", , 3, "e*g") Find b, followed by any number of any characters (including no character), followed by c. The string found must appear immediately before e followed by any number of any characters (including no character), followed by g. Return the string found. #VALUE!
=itSEARCH("abcdefgh", "b*c", , 3, "*e*g") Find b, followed by any number of any characters (including no character), followed by c. The string found must appear before any number of any characters (including no character) followed by e, followed by any number of any characters (including no character), followed by g. Return the string found. bc
=itSEARCH("abcdefgh", "b*c", , 3, "*y*z") Find b, followed by any number of any characters (including no character), followed by c. The string found must appear immediately before any number of any characters (including no character), followed by y, followed by any number of any characters (including no character), followed by z. Return the string found. #VALUE!

find_not_before_text  Examples:
=itSEARCH("abcdefgh", "b*c", , 3, , "d*g") Find b, followed by any number of any characters (including no character), followed by c. The string found must not appear immediately before d followed by any number of any characters (including no character), followed by g. Return the string found. #VALUE!
=itSEARCH("abcdefgh", "b*c", , 3, , "e*g") Find b, followed by any number of any characters (including no character), followed by c. The string found must not appear immediately before e followed by any number of any characters (including no character), followed by g. Return the string found. bc
=itSEARCH("abcdefgh", "b*c", , 3, , "*e*g") Find b, followed by any number of any characters (including no character), followed by c. The string found must not appear before any number of any characters (including no character), followed by e, followed by any number of any characters (including no character), followed by g. Return the string found. #VALUE!
=itSEARCH("abcdefgh", "b*c", , 3, , "*y*z") Find b, followed by any number of any characters (including no character), followed by c. The string found must not appear immediately before any number of any characters (including no character), followed by y, followed by any number of any characters (including no character), followed by z. Return the string found. bc

return_position_offset and return_length_offset Examples:
=itSEARCH("abcdefghi", "c*e", , 3, , , 1) Find b, followed by any number of any characters (including no character), followed by c. Move the position of the string found 1 character to the right. Return the resulting string. def
=itSEARCH("abcdefghi", "c*e", , 3, , , , 1) Find b, followed by any number of any characters (including no character), followed by c. Add 1 character to the length of the string found. Return the resulting string. cdef
=itSEARCH("abcdefghi", "c*e", , 3, , , -1, -1) Find b, followed by any number of any characters (including no character), followed by c. Move the position of the string found 1 character to the left, and subtract 1 character from the length of the string found. Return the resulting string. bc

search_from_right Examples:
=itSEARCH("abcde", "b", , 1, , , , , TRUE) Find b, searching from right to left, and return its position number, counting from the right. 4
=itSEARCH("abcde", "ab", , 1, , , , , TRUE) Find ab, searching from right to left, and return its position number, counting from the right. #VALUE!
(Although ab would be found searching from left to right, it is not found searching from right to left.)
=itSEARCH("abcde", "ba", , 1, , , , , TRUE) Find ba, searching from right to left, and return its position number, counting from the right. 4
(Although ba would not be found searching from left to right, it is found searching from right to left.)
=itSEARCH("abcde", "ba", , 2, , , , , TRUE) Find ba, searching from right to left, and return its position number, counting from the left. 1
(Although ba would not be found searching from left to right, it is found searching from right to left.)

not_found_return Example:
=itSEARCH("abc", "e", , , , , , , , "Not Found") Find e, and if not found return "Not Found" instead of the default "#VALUE!" Not Found

 

PrecisionCalc Home Page