LO Calc won't strip quotation marks from text
Since quotation marks serve a special function, treating them as a character requires workarounds. There are a number of standard methods, such as doubling the quotation mark or using CHAR(32), its ASCII code.
I needed to strip quotes from around text, and tried every method I could find to represent the quote character in a formula, but nothing worked. LO Calc displayed the quotation marks, but all attempts to find or compare them in a formula failed to recognize them.
Diagnostics
- I thought perhaps that was an item where the Excel methods didn't transfer to LO Calc. But Googling revealed that is not the case; the same methods should work in Calc.
- I checked to verify the character code for the quotation marks. The quote symbol typed into Calc was not being stored as character 32, but as character 226.
- It was not a keyboard issue. Typing a quote mark elsewhere checked out as character 32.
- Character 226 isn't even a left or right quote (characters 147 and 148), it's the code for an accented character not used in English,
â. - Since the stored code was for a foreign language character, I verified that all of the language and locale settings were US English (and they were).
- Calc still treats the character as a quote mark for purposes such as designating text. But it doesn't behave as either a regular character or a quote mark for handling or use in a formula. It can't be used singly as a character for comparison, and it can't be doubled, either.
I'll post this as a self-answered question in case anyone else runs into the same situation.
worksheet-function libreoffice-calc special-characters
asked Jan 3 at 9:00
fixer1234fixer1234
18.7k144982
add a comment |
Since quotation marks serve a special function, treating them as a character requires workarounds. There are a number of standard methods, such as doubling the quotation mark or using CHAR(32), its ASCII code.
I needed to strip quotes from around text, and tried every method I could find to represent the quote character in a formula, but nothing worked. LO Calc displayed the quotation marks, but all attempts to find or compare them in a formula failed to recognize them.
Diagnostics
- I thought perhaps that was an item where the Excel methods didn't transfer to LO Calc. But Googling revealed that is not the case; the same methods should work in Calc.
- I checked to verify the character code for the quotation marks. The quote symbol typed into Calc was not being stored as character 32, but as character 226.
- It was not a keyboard issue. Typing a quote mark elsewhere checked out as character 32.
- Character 226 isn't even a left or right quote (characters 147 and 148), it's the code for an accented character not used in English,
â. - Since the stored code was for a foreign language character, I verified that all of the language and locale settings were US English (and they were).
- Calc still treats the character as a quote mark for purposes such as designating text. But it doesn't behave as either a regular character or a quote mark for handling or use in a formula. It can't be used singly as a character for comparison, and it can't be doubled, either.
I'll post this as a self-answered question in case anyone else runs into the same situation.
worksheet-function libreoffice-calc special-characters
asked Jan 3 at 9:00
fixer1234fixer1234
18.7k144982
add a comment |
Since quotation marks serve a special function, treating them as a character requires workarounds. There are a number of standard methods, such as doubling the quotation mark or using CHAR(32), its ASCII code.
I needed to strip quotes from around text, and tried every method I could find to represent the quote character in a formula, but nothing worked. LO Calc displayed the quotation marks, but all attempts to find or compare them in a formula failed to recognize them.
Diagnostics
- I thought perhaps that was an item where the Excel methods didn't transfer to LO Calc. But Googling revealed that is not the case; the same methods should work in Calc.
- I checked to verify the character code for the quotation marks. The quote symbol typed into Calc was not being stored as character 32, but as character 226.
- It was not a keyboard issue. Typing a quote mark elsewhere checked out as character 32.
- Character 226 isn't even a left or right quote (characters 147 and 148), it's the code for an accented character not used in English,
â. - Since the stored code was for a foreign language character, I verified that all of the language and locale settings were US English (and they were).
- Calc still treats the character as a quote mark for purposes such as designating text. But it doesn't behave as either a regular character or a quote mark for handling or use in a formula. It can't be used singly as a character for comparison, and it can't be doubled, either.
I'll post this as a self-answered question in case anyone else runs into the same situation.
worksheet-function libreoffice-calc special-characters
asked Jan 3 at 9:00
fixer1234fixer1234
18.7k144982
Since quotation marks serve a special function, treating them as a character requires workarounds. There are a number of standard methods, such as doubling the quotation mark or using CHAR(32), its ASCII code.
I needed to strip quotes from around text, and tried every method I could find to represent the quote character in a formula, but nothing worked. LO Calc displayed the quotation marks, but all attempts to find or compare them in a formula failed to recognize them.
Diagnostics
- I thought perhaps that was an item where the Excel methods didn't transfer to LO Calc. But Googling revealed that is not the case; the same methods should work in Calc.
- I checked to verify the character code for the quotation marks. The quote symbol typed into Calc was not being stored as character 32, but as character 226.
- It was not a keyboard issue. Typing a quote mark elsewhere checked out as character 32.
- Character 226 isn't even a left or right quote (characters 147 and 148), it's the code for an accented character not used in English,
â. - Since the stored code was for a foreign language character, I verified that all of the language and locale settings were US English (and they were).
- Calc still treats the character as a quote mark for purposes such as designating text. But it doesn't behave as either a regular character or a quote mark for handling or use in a formula. It can't be used singly as a character for comparison, and it can't be doubled, either.
I'll post this as a self-answered question in case anyone else runs into the same situation.
worksheet-function libreoffice-calc special-characters
worksheet-function libreoffice-calc special-characters
asked Jan 3 at 9:00
fixer1234fixer1234
18.7k144982
asked Jan 3 at 9:00
fixer1234fixer1234
18.7k144982
asked Jan 3 at 9:00
fixer1234fixer1234
18.7k144982
asked Jan 3 at 9:00
fixer1234fixer1234
18.7k144982
asked Jan 3 at 9:00
fixer1234fixer1234
18.7k144982
18.7k144982
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Calc uses smart quotes by default. It auto-corrects normal quotes to smart quotes. Both left and right smart quotes are stored as the same character (226).
Solution
- From the menu:
Tools | Autocorrect Options - Deselect
Replacefor Single Quotes and Double Quotes
answered Jan 3 at 9:00
fixer1234fixer1234
18.7k144982
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1390089%2flo-calc-wont-strip-quotation-marks-from-text%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
Calc uses smart quotes by default. It auto-corrects normal quotes to smart quotes. Both left and right smart quotes are stored as the same character (226).
Solution
- From the menu:
Tools | Autocorrect Options - Deselect
Replacefor Single Quotes and Double Quotes
answered Jan 3 at 9:00
fixer1234fixer1234
18.7k144982
add a comment |
Calc uses smart quotes by default. It auto-corrects normal quotes to smart quotes. Both left and right smart quotes are stored as the same character (226).
Solution
- From the menu:
Tools | Autocorrect Options - Deselect
Replacefor Single Quotes and Double Quotes
answered Jan 3 at 9:00
fixer1234fixer1234
18.7k144982
add a comment |
Calc uses smart quotes by default. It auto-corrects normal quotes to smart quotes. Both left and right smart quotes are stored as the same character (226).
Solution
- From the menu:
Tools | Autocorrect Options - Deselect
Replacefor Single Quotes and Double Quotes
answered Jan 3 at 9:00
fixer1234fixer1234
18.7k144982
Calc uses smart quotes by default. It auto-corrects normal quotes to smart quotes. Both left and right smart quotes are stored as the same character (226).
Solution
- From the menu:
Tools | Autocorrect Options - Deselect
Replacefor Single Quotes and Double Quotes
answered Jan 3 at 9:00
fixer1234fixer1234
18.7k144982
answered Jan 3 at 9:00
fixer1234fixer1234
18.7k144982
answered Jan 3 at 9:00
fixer1234fixer1234
18.7k144982
answered Jan 3 at 9:00
fixer1234fixer1234
18.7k144982
18.7k144982
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1390089%2flo-calc-wont-strip-quotation-marks-from-text%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
