PrecisionCalc
inspector
text
Do Anything with Text in Formulas
itCOUNTINCELL
Counts the occurrences of a given string in a worksheet cell, or within any text string, allowing versatile and flexible criteria. Can be case sensitive or not.
Syntax
itCOUNTINCELL(cell_reference,find_text,case_sensitive)
| cell_reference | Required. The worksheet cell or text string within which to search. | ||
| find_text | Required. The
text to count 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:
|
||
| 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: |
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: |
|
| [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. | ||
Remarks
Wildcards and other substitutions can be combined and repeated in find_text.
itCOUNTINCELL is a simplified and specialized version if itSEARCH.
Examples
| Formula | Description | Result |
| =itCOUNTINCELL("ababa", "b") | Count the occurrences of b in ababa. | 2 |
| =itCOUNTINCELL(A1, B1) | Count the occurrences of cell B1 within the contents of cell A1. | Depends on the contents of cells A1 and B1. |
|
Wildcard and Substitution Examples: |
||
| =itCOUNTINCELL("abc", "?b") | Count the occurrences of b, and one character before b. | 1 |
| =itCOUNTINCELL("babc", "?b") | Count the occurrences of b, and one character before b. | 1 |
| =itCOUNTINCELL("babc", "*b") | Count the occurrences of b, and any number of characters (including no character) before b. | 2 |
| =itCOUNTINCELL("better bat", "b(e|a)t") | Count the occurrences of b, followed by either e or a, then followed by t, in "better bat". | 2 |
| =itCOUNTINCELL("bitter bat", "b(e|a)t") | Count the occurrences of b, followed by either e or a, then followed by t, in "bitter bat". | 1 |
| =itCOUNTINCELL("abcdefg", "[b-e]") | Count the occurrences of b, or c, or d, or e. | 4 |
| =itCOUNTINCELL("babbbcbbbbb", "b{2,}") | Count the occurrences of 2 or more b's in a row. | 2 |
| =itCOUNTINCELL("babbbbbc", "b{2,4}") | Count the occurrences of 2 - 4 b's in a row. | 1 |
| =itCOUNTINCELL("bbbbbb", "b{2,4}") | Count the occurrences of 2 - 4 b's in a row. | 2 (First it counts bbbb, then it counts bb.) |
| =itCOUNTINCELL("abcbdbeb", "b[^d]") | Count the occurrences of b followed by any character other than d. | 2 (Counts bc and be, but does not count bd or b.) |
| =itCOUNTINCELL("bcbdbe", "b[^b-d]") | Count the occurrences of b followed by any one character other than b, c, or d. | 1 |
| =itCOUNTINCELL("!@?#$%^?&", "\?") | Count the occurrences of a question mark character. | 2 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. |
| =itCOUNTINCELL("xabqzcnum2xyz", "?b*c(m?n[1-3]|n?m[1-3])[^a-c]") | This example shows one way of combining various wildcards and substitutions. | 1 |
| =itCOUNTINCELL("qacbdgxyz", "a[^b][^c](d|e)(f|g)??") | This example shows one way of repeating wildcards and substitutions. | 1 |
|
case_sensitive Examples: |
||
| =itCOUNTINCELL("abc", "B") | Count the occurrences of B (not b). | 0 |
| =itCOUNTINCELL("abc", "B", FALSE) | Count the occurrences of B or b. | 1 |