PrecisionCalc
inspector
text
Do Anything with Text in Formulas
itREPLACE
Allows extremely versatile and flexible text replacement. Locates one or more text strings within another text string, and replaces it (or them) with other specified text. Can also rearrange found text. Can be case sensitive or not. Can search from left or from right.
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:
|
||
| 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. |
|
| replace_text | Required. The
text with which to replace find_text.
Rearranging text: These variables are named "$1", "$2", "$3", etc. They are numbered in order of their respective left parenthesis in find_text. See replace_all examples below. |
||
| 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_all | Optional. Determines whether replace_text replaces all strings found, or only the first string found. Set to TRUE to replace all strings found. FALSE by default. | ||
| 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:
|
||
| 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 find_text is not found. #VALUE! by default. | ||
Remarks
In find_text, find_before_text, and find_not_before_text, wildcards and other substitutions can be combined and repeated.
Examples
| Formula | Description | Result |
| =itREPLACE("abcde", "b", "x") | Find b, and replace it with x. | axcde |
| =itREPLACE(A1, B1, C1) | Find the contents of cell B1 within the contents of cell A1, and replace it with the contents of cell C1. | Depends on the contents of cells A1, B1, and C1. |
|
Wildcard and Substitution Examples: |
||
| =itREPLACE("abcde", "?d", "x") | Find d, and one character before d, and replace it with x. | abxe |
| =itREPLACE("abcde", "*d", "x") | Find d, and any number of characters (including no character) before d, and replace it with x. | xe |
| =itREPLACE("better bat", "b(e|a)t", "x") | Find b, followed by either e or a, then followed by t, in "better bat", and replace the first string found with x. | xter bat |
| =itREPLACE("bitter bat", "b(e|a)t", "x") | Find b, followed by either e or a, then followed by t, in "bitter bat", and replace the first string found with x. | bitter x |
| =itREPLACE("abcdefg", "[b-e]", "x") | Find b, or c, or d, or e, and replace with x. | axcdefg |
| =itREPLACE("babbbcbbbbb", "b{2,}", "x") | Find 2 or more b's in a row, and replace the first string found with x. | baxcbbbbb |
| =itREPLACE("babbbbbc", "b{2,4}", "x") | Find 2 - 4 b's in a row, and replace the first string found with x. | #VALUE! (No match found.) |
| =itREPLACE("abcde", "b[^d]", "x") | Find b followed by any character other than d, and replace the first string found with x. | axde |
| =itREPLACE("bcbdbe", "b[^b-d]", "x") | Find b followed by any one character other than b, c, or d, and replace the first string found with x. | bcbdx |
| =itREPLACE("!@?#$%^?&", "\?", "x") | Find a question mark character, and replace the first one found with an x. | !@x#$%^?& 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. |
| =itREPLACE("xabqzcnum2xyz", "?b*c(m?n[1-3]|n?m[1-3])[^a-c]", "@") | This example shows one way of combining various wildcards and substitutions. | x@yz |
| =itREPLACE("qacbdgxyz", "a[^b][^c](d|e)(f|g)??", "@") | This example shows one way of repeating wildcards and substitutions. | q@yz |
|
case_sensitive Examples: |
||
| =itREPLACE("abc", "B", "x") | Find B (not b), and replace it with x. | #VALUE! |
| =itREPLACE("abc", "B", "x", FALSE) | Find B or b, and replace it with x. | axc |
|
replace_all Examples: |
||
| =itREPLACE("babcb", "b", "x", , TRUE) | Find b, and return all strings found with x. | xaxcx |
| =itREPLACE("abcabacabaaca", "b*c", "x", , TRUE) | Find b, followed by any number of any characters, then c, and replace all strings found with x. | axaxaxa |
|
find_before_text Examples: |
||
| =itREPLACE("abcdefgh", "b*c", "x", , , "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. Replace the first string found with x. | axdefgh |
| =itREPLACE("abcdefgh", "b*c", "x", , , "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. Replace the first string found with x. | #VALUE! |
| =itREPLACE("abcdefgh", "b*c", "x", , , "*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. Replace the first string found with x. | axdefgh |
| =itREPLACE("abcdefgh", "b*c", "x", , , "*y*z") | 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 y, followed by any number of any characters (including no character), followed by z. Replace the first string found with x. | #VALUE! |
|
find_not_before_text Examples: |
||
| =itREPLACE("abcdefgh", "b*c", "x", , , , "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. Replace the first string found with x. | #VALUE! |
| =itREPLACE("abcdefgh", "b*c", "x", , , , "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. Replace the first string found with x. | axdefgh |
| =itREPLACE("abcdefgh", "b*c", "x", , , , "*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. Replace the first string found with x. | #VALUE! |
| =itREPLACE("abcdefgh", "b*c", "x", , , , "*y*z") | 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 y, followed by any number of any characters (including no character), followed by z. Replace the first string found with x. | axdefgh |
|
search_from_right Examples: |
||
| =itREPLACE("ababa", "b", "x", , , , , TRUE) | Find b, searching from right to left, and replace the first one found with x. | abaxa |
| =itREPLACE("abcde", "ab", "x", , , , , TRUE) | Find ab, searching from right to left, and replace the first string found with x. | #VALUE! (Although ab would be found searching from left to right, it is not found searching from right to left.) |
| =itREPLACE("abcde", "ba", "x", , , , , TRUE) | Find ba, searching from right to left, and replace the first string found with x. | xcde (Although ba would not be found searching from left to right, it is found searching from right to left.) |
|
not_found_return Example: |
||
| =itREPLACE("abc", "e", "x", , , , , , "Not Found") | Find e, and replace the first one found with x. If not found, return "Not Found" instead of the default "#VALUE!" | Not Found |