Mixing
![Prove that $1<frac{1}{n+1}+frac{1}{n+2}+…+frac{1}{3n+2}<2$ [duplicate]](https://lh3.googleusercontent.com/-nb-bKUb65Us/AAAAAAAAAAI/AAAAAAAAAAA/AGDgw-g7bleOgMTZpo4vMVk6D-3aJLfKyw/s72-c-mo/photo.jpg?sz=32)
Literal String Search containing Characters
As part of a countif I'm trying to search for a cell contents that may contain {}. It also needs to be constrained to an exact match within the range. So my first hunch is to go with regexmatch but I'm having issues with the {} becoming part of the regex. I basically need a way of escaping the entire string from the cell. Here's what I've been trying as an example:
REGEXMATCH(A2,"(?:^|s)("&A1&")(?:s)")
So say A1 is blue{car} and A2 is like a blue{car}, this should match. However I just get a #REF! error as its trying to use the {}as a quantifier. I know you can escape characters in a predetermined string but as A1 could actually be anything I need a general approach. It seems Q…E wrapped around "&A1&" would be perfect but unfortunately its unsupported in sheets.
Any assistance with this gratefully received!
regex google-sheets
asked Nov 21 '18 at 10:53
Chris BarrettChris Barrett
867
add a comment |
As part of a countif I'm trying to search for a cell contents that may contain {}. It also needs to be constrained to an exact match within the range. So my first hunch is to go with regexmatch but I'm having issues with the {} becoming part of the regex. I basically need a way of escaping the entire string from the cell. Here's what I've been trying as an example:
REGEXMATCH(A2,"(?:^|s)("&A1&")(?:s)")
So say A1 is blue{car} and A2 is like a blue{car}, this should match. However I just get a #REF! error as its trying to use the {}as a quantifier. I know you can escape characters in a predetermined string but as A1 could actually be anything I need a general approach. It seems Q…E wrapped around "&A1&" would be perfect but unfortunately its unsupported in sheets.
Any assistance with this gratefully received!
regex google-sheets
asked Nov 21 '18 at 10:53
Chris BarrettChris Barrett
867
add a comment |
As part of a countif I'm trying to search for a cell contents that may contain {}. It also needs to be constrained to an exact match within the range. So my first hunch is to go with regexmatch but I'm having issues with the {} becoming part of the regex. I basically need a way of escaping the entire string from the cell. Here's what I've been trying as an example:
REGEXMATCH(A2,"(?:^|s)("&A1&")(?:s)")
So say A1 is blue{car} and A2 is like a blue{car}, this should match. However I just get a #REF! error as its trying to use the {}as a quantifier. I know you can escape characters in a predetermined string but as A1 could actually be anything I need a general approach. It seems Q…E wrapped around "&A1&" would be perfect but unfortunately its unsupported in sheets.
Any assistance with this gratefully received!
regex google-sheets
asked Nov 21 '18 at 10:53
Chris BarrettChris Barrett
867
As part of a countif I'm trying to search for a cell contents that may contain {}. It also needs to be constrained to an exact match within the range. So my first hunch is to go with regexmatch but I'm having issues with the {} becoming part of the regex. I basically need a way of escaping the entire string from the cell. Here's what I've been trying as an example:
REGEXMATCH(A2,"(?:^|s)("&A1&")(?:s)")
So say A1 is blue{car} and A2 is like a blue{car}, this should match. However I just get a #REF! error as its trying to use the {}as a quantifier. I know you can escape characters in a predetermined string but as A1 could actually be anything I need a general approach. It seems Q…E wrapped around "&A1&" would be perfect but unfortunately its unsupported in sheets.
Any assistance with this gratefully received!
regex google-sheets
regex google-sheets
asked Nov 21 '18 at 10:53
Chris BarrettChris Barrett
867
asked Nov 21 '18 at 10:53
Chris BarrettChris Barrett
867
asked Nov 21 '18 at 10:53
Chris BarrettChris Barrett
867
asked Nov 21 '18 at 10:53
Chris BarrettChris Barrett
867
asked Nov 21 '18 at 10:53
Chris BarrettChris Barrett
867
867
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Based on What special characters must be escaped in regular expressions? and RE2 reference, you may easily write a function to escape all special chars that must be treated as literal chars in the regex pattern:
=REGEXREPLACE(A1, "[.^$*+?()[{\|]", "\$0")
For {car}, it will yield {car} and will only match {car}. Note that RE2 regex engine is smart enough to parse the braces in {car} as literal braces. The only problem with a non-escaped { will appear if the contents between braces is numeric and is located after a non-quantifyable pattern, say, at the start of a string or after an alternation operator.
There is another problem in your regex: you require whitespace or start of string before {car} and a whitespace after your {car}, but blue{car} has no whitespaces at either end and is not at the start of the string. Please reconsider your requirements and amend the pattern accordingly.
answered Nov 21 '18 at 11:04
Wiktor StribiżewWiktor Stribiżew
315k16133213
1
Thanks. This is perfect. The RegEx I posted was a little incomplete as I was umming and ahhing about whitespace in the matches but your solution cures both issues. Very Clever! thanks man.
– Chris Barrett
Nov 21 '18 at 11:58
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53410545%2fliteral-string-search-containing-characters%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Based on What special characters must be escaped in regular expressions? and RE2 reference, you may easily write a function to escape all special chars that must be treated as literal chars in the regex pattern:
=REGEXREPLACE(A1, "[.^$*+?()[{\|]", "\$0")
For {car}, it will yield {car} and will only match {car}. Note that RE2 regex engine is smart enough to parse the braces in {car} as literal braces. The only problem with a non-escaped { will appear if the contents between braces is numeric and is located after a non-quantifyable pattern, say, at the start of a string or after an alternation operator.
There is another problem in your regex: you require whitespace or start of string before {car} and a whitespace after your {car}, but blue{car} has no whitespaces at either end and is not at the start of the string. Please reconsider your requirements and amend the pattern accordingly.
answered Nov 21 '18 at 11:04
Wiktor StribiżewWiktor Stribiżew
315k16133213
1
Thanks. This is perfect. The RegEx I posted was a little incomplete as I was umming and ahhing about whitespace in the matches but your solution cures both issues. Very Clever! thanks man.
– Chris Barrett
Nov 21 '18 at 11:58
add a comment |
Based on What special characters must be escaped in regular expressions? and RE2 reference, you may easily write a function to escape all special chars that must be treated as literal chars in the regex pattern:
=REGEXREPLACE(A1, "[.^$*+?()[{\|]", "\$0")
For {car}, it will yield {car} and will only match {car}. Note that RE2 regex engine is smart enough to parse the braces in {car} as literal braces. The only problem with a non-escaped { will appear if the contents between braces is numeric and is located after a non-quantifyable pattern, say, at the start of a string or after an alternation operator.
There is another problem in your regex: you require whitespace or start of string before {car} and a whitespace after your {car}, but blue{car} has no whitespaces at either end and is not at the start of the string. Please reconsider your requirements and amend the pattern accordingly.
answered Nov 21 '18 at 11:04
Wiktor StribiżewWiktor Stribiżew
315k16133213
1
Thanks. This is perfect. The RegEx I posted was a little incomplete as I was umming and ahhing about whitespace in the matches but your solution cures both issues. Very Clever! thanks man.
– Chris Barrett
Nov 21 '18 at 11:58
add a comment |
Based on What special characters must be escaped in regular expressions? and RE2 reference, you may easily write a function to escape all special chars that must be treated as literal chars in the regex pattern:
=REGEXREPLACE(A1, "[.^$*+?()[{\|]", "\$0")
For {car}, it will yield {car} and will only match {car}. Note that RE2 regex engine is smart enough to parse the braces in {car} as literal braces. The only problem with a non-escaped { will appear if the contents between braces is numeric and is located after a non-quantifyable pattern, say, at the start of a string or after an alternation operator.
There is another problem in your regex: you require whitespace or start of string before {car} and a whitespace after your {car}, but blue{car} has no whitespaces at either end and is not at the start of the string. Please reconsider your requirements and amend the pattern accordingly.
answered Nov 21 '18 at 11:04
Wiktor StribiżewWiktor Stribiżew
315k16133213
Based on What special characters must be escaped in regular expressions? and RE2 reference, you may easily write a function to escape all special chars that must be treated as literal chars in the regex pattern:
=REGEXREPLACE(A1, "[.^$*+?()[{\|]", "\$0")
For {car}, it will yield {car} and will only match {car}. Note that RE2 regex engine is smart enough to parse the braces in {car} as literal braces. The only problem with a non-escaped { will appear if the contents between braces is numeric and is located after a non-quantifyable pattern, say, at the start of a string or after an alternation operator.
There is another problem in your regex: you require whitespace or start of string before {car} and a whitespace after your {car}, but blue{car} has no whitespaces at either end and is not at the start of the string. Please reconsider your requirements and amend the pattern accordingly.
answered Nov 21 '18 at 11:04
Wiktor StribiżewWiktor Stribiżew
315k16133213
edited Nov 21 '18 at 11:13
answered Nov 21 '18 at 11:04
Wiktor StribiżewWiktor Stribiżew
315k16133213
answered Nov 21 '18 at 11:04
Wiktor StribiżewWiktor Stribiżew
315k16133213
answered Nov 21 '18 at 11:04
Wiktor StribiżewWiktor Stribiżew
315k16133213
315k16133213
1
Thanks. This is perfect. The RegEx I posted was a little incomplete as I was umming and ahhing about whitespace in the matches but your solution cures both issues. Very Clever! thanks man.
– Chris Barrett
Nov 21 '18 at 11:58
add a comment |
1
Thanks. This is perfect. The RegEx I posted was a little incomplete as I was umming and ahhing about whitespace in the matches but your solution cures both issues. Very Clever! thanks man.
– Chris Barrett
Nov 21 '18 at 11:58
1
1
Thanks. This is perfect. The RegEx I posted was a little incomplete as I was umming and ahhing about whitespace in the matches but your solution cures both issues. Very Clever! thanks man.
– Chris Barrett
Nov 21 '18 at 11:58
Thanks. This is perfect. The RegEx I posted was a little incomplete as I was umming and ahhing about whitespace in the matches but your solution cures both issues. Very Clever! thanks man.
– Chris Barrett
Nov 21 '18 at 11:58
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53410545%2fliteral-string-search-containing-characters%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
