PrecisionCalc
inspector
text
Do Anything with Text in Formulas
itGETWORD
Returns the nth word in a string. Allows replacing word separators (spaces and tabs by default) with versatile and flexible search string. Can be case sensitive or not. Can search from left or from right.
Syntax
itGETWORD(string_to_search,nth_word,word_separator,case_sensitive,search_from_right,not_found_return)
| string_to_search | Required. The text string in which to find the nth word. | ||
| nth_word | Required. The number of the word to find. | ||
| word_separator | Optional. The
separator or separators between the words. Spaces and tabs by default. 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 word_separator distinguishes between lower case and upper case. Set to False to ignore case differences. True by default. | ||
| search_from_right | Optional.
If True, searches from right to left instead of from left to right. False by default. 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 nth word is not found. #VALUE! by default. | ||
Remarks
Wildcards and other substitutions can be combined and repeated in word_separator.
itGETWORD is a simplified and specialized version if itSEARCH.
Examples
| Formula | Description | Result |
| =itGETWORD("Dr. Jane Smith, Brain Surgeon and Rocket Scientist", 4) | Get the 4th word. | Brain |
| =itGETWORD(A1, B1) | Get the nth word in cell B1, according to the number in cell A1. | Depends on the contents of cells A1 and B1. |
|
word_separator Example: |
||
| =itGETWORD("aa bb, cc", 2, "(, | ,| |,)") | Get the 2nd word, separating words by a comma and/or a space character (comma then space, or space then comma, or space, or comma). | bb |
|
case_sensitive Examples: |
||
| =itGETWORD("aaxbbxcc", 2, "X") | Get the 2nd word, separating words by X (not x). | #VALUE! |
| =itGETWORD("aaxbbxcc", 2, "X", FALSE) | Get the 2nd word, separating words by X or x. | bb |
|
search_from_right Examples: |
||
| =itGETWORD("Dr. Jane Smith, Brain Surgeon and Rocket Scientist", 4, , , TRUE) | Get the 4th word, counting from the right. | Surgeon |
| =itGETWORD("a, b, c, d, e", 3, ", ", , TRUE) | Get the 4th word, separating words by a comma, then a space, and counting from the right. | #VALUE! (Although ", " (comma, then space) would be found searching from left to right, it is not found searching from right to left.) |
| =itGETWORD("a, b, c, d, e", 3, " ,", , TRUE) | Get the 4th word, separating words by a space, then a comma, and counting from the right. | c (Although " ," (space, then comma) would not be found searching from left to right, it is found searching from right to left.) |
|
not_found_return Example: |
||
| =itGETWORD("Dr. Jane Smith, Brain Surgeon and Rocket Scientist", 20, , , , "Not Found") | Get the 20th word, and if not found return "Not Found" instead of the default "#VALUE!" | Not Found |