How do I force excel to display (potential) expressions as text, instead of attempt to evaluate them?
I've got an excel worksheet that contains (string) cell values like this
=-L
=-N
=-R
etc
Now, excel sees the "=" character and attempts to evaluate the expression. It cannot, and displays the following instead.
#NAME?
I would like the actual string "=-L" to be displayed instead.
I attempted to highlight the entire column and do
right click --> format cells --> Text --> OK
But the column didn't automatically update. I still see
#NAME?
where I should now be seeing
=-L
Now... if I double click the cell and then navigate elsewhere, it looks like the text value that is being displayed updates. However, I want the entire workbook to update so that I don't need to go through the hundreds of cells double clicking. I tried
F9
Shift + F9
ctrl + alt + Shift + F9
ctrl + alt + F9
etc.
Is there a way this can be done?
microsoft-excel worksheet-function
asked Jan 9 at 20:22
ZackZack
146116
add a comment |
I've got an excel worksheet that contains (string) cell values like this
=-L
=-N
=-R
etc
Now, excel sees the "=" character and attempts to evaluate the expression. It cannot, and displays the following instead.
#NAME?
I would like the actual string "=-L" to be displayed instead.
I attempted to highlight the entire column and do
right click --> format cells --> Text --> OK
But the column didn't automatically update. I still see
#NAME?
where I should now be seeing
=-L
Now... if I double click the cell and then navigate elsewhere, it looks like the text value that is being displayed updates. However, I want the entire workbook to update so that I don't need to go through the hundreds of cells double clicking. I tried
F9
Shift + F9
ctrl + alt + Shift + F9
ctrl + alt + F9
etc.
Is there a way this can be done?
microsoft-excel worksheet-function
asked Jan 9 at 20:22
ZackZack
146116
Add a'to the start of the cell.
– DavidPostill♦
Jan 9 at 20:43
Is there a way to do this without going through each cell and prepending a single quote?
– Zack
Jan 9 at 20:47
No idea .......
– DavidPostill♦
Jan 9 at 20:49
add a comment |
I've got an excel worksheet that contains (string) cell values like this
=-L
=-N
=-R
etc
Now, excel sees the "=" character and attempts to evaluate the expression. It cannot, and displays the following instead.
#NAME?
I would like the actual string "=-L" to be displayed instead.
I attempted to highlight the entire column and do
right click --> format cells --> Text --> OK
But the column didn't automatically update. I still see
#NAME?
where I should now be seeing
=-L
Now... if I double click the cell and then navigate elsewhere, it looks like the text value that is being displayed updates. However, I want the entire workbook to update so that I don't need to go through the hundreds of cells double clicking. I tried
F9
Shift + F9
ctrl + alt + Shift + F9
ctrl + alt + F9
etc.
Is there a way this can be done?
microsoft-excel worksheet-function
asked Jan 9 at 20:22
ZackZack
146116
I've got an excel worksheet that contains (string) cell values like this
=-L
=-N
=-R
etc
Now, excel sees the "=" character and attempts to evaluate the expression. It cannot, and displays the following instead.
#NAME?
I would like the actual string "=-L" to be displayed instead.
I attempted to highlight the entire column and do
right click --> format cells --> Text --> OK
But the column didn't automatically update. I still see
#NAME?
where I should now be seeing
=-L
Now... if I double click the cell and then navigate elsewhere, it looks like the text value that is being displayed updates. However, I want the entire workbook to update so that I don't need to go through the hundreds of cells double clicking. I tried
F9
Shift + F9
ctrl + alt + Shift + F9
ctrl + alt + F9
etc.
Is there a way this can be done?
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Jan 9 at 20:22
ZackZack
146116
asked Jan 9 at 20:22
ZackZack
146116
asked Jan 9 at 20:22
ZackZack
146116
asked Jan 9 at 20:22
ZackZack
146116
asked Jan 9 at 20:22
ZackZack
146116
146116
Add a'to the start of the cell.
– DavidPostill♦
Jan 9 at 20:43
Is there a way to do this without going through each cell and prepending a single quote?
– Zack
Jan 9 at 20:47
No idea .......
– DavidPostill♦
Jan 9 at 20:49
add a comment |
Add a'to the start of the cell.
– DavidPostill♦
Jan 9 at 20:43
Is there a way to do this without going through each cell and prepending a single quote?
– Zack
Jan 9 at 20:47
No idea .......
– DavidPostill♦
Jan 9 at 20:49
Add a
' to the start of the cell.– DavidPostill♦
Jan 9 at 20:43
Add a
' to the start of the cell.– DavidPostill♦
Jan 9 at 20:43
Is there a way to do this without going through each cell and prepending a single quote?
– Zack
Jan 9 at 20:47
Is there a way to do this without going through each cell and prepending a single quote?
– Zack
Jan 9 at 20:47
No idea .......
– DavidPostill♦
Jan 9 at 20:49
No idea .......
– DavidPostill♦
Jan 9 at 20:49
add a comment |
3 Answers
3
active
oldest
votes
Do a Find and Replace for = with '= and Replace All.
If your sheet has real formulas that you want to retain then you could limit the Find and Replace by selecting the cells that you want to change first. One way to do this would be to use Go To Special - hit F5 and specify Formulas with Error checkbox checked and the other checkboxes cleared.
answered Jan 9 at 21:50
Mark FitzgeraldMark Fitzgerald
3981211
Thank you Mark. A good answer to be sure. I was able to just click "Show Formulas" and save the resulting view to a csv. Then, when I ran a search for "#NAME?" in the resulting .csv file, it was nowhere to be found.
– Zack
Jan 10 at 13:22
add a comment |
Replace:
=-L
with:
="=-L"
etc.
answered Jan 9 at 22:48
Gary's StudentGary's Student
13.5k31730
add a comment |
I asked this on another post and it got answered there. While prepending a single quote to the front of the string DOES do what I want, I didn't want to have to do this for every cell.
For my issue
Under Formulas in the Ribbon, Under (technically over) Formula Auditing, select Show Formulas. This applies to the entire worksheet.
Was enough to do what I want. Note, that this will effect the entire worksheet and not a single column. For my purposes, this was fine.
answered Jan 9 at 23:01
ZackZack
146116
Clever, but that won't work in this case. The OP needs the entire expression as a text string, presumably as input for other formulas. This solution would leave it as a formula, which in use, would output an error.
– fixer1234
Jan 9 at 23:32
I am the OP lol
– Zack
Jan 10 at 13:19
I didn't even catch that. :-) But if this solution is satisfactory for you, it means the question is a bit misleading. This is the solution to a different requirement, and doesn't really solve the issue of the error result which is a key element described in the question. That's why it never dawned on me to notice that you are the OP. At least you didn't accept this answer, which would have been really confusing for readers who come here with the problem described in the question. Well, this won't be the first time that someone discovered that what they originally thought (cont'd)
– fixer1234
Jan 10 at 13:48
were problem constraints turned out not to be. For the sake of other readers coming here for an answer, can you add a few sentences of explanation to the question and this answer? This answer will make more sense in the context of discovering a change in requirements. But for the question and answers to have the most value, it would be even better to add a new question that describes your current requirements and move this answer there. That way, there would be two different questions, both with appropriate and good answers.
– fixer1234
Jan 10 at 13:48
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%2f1392433%2fhow-do-i-force-excel-to-display-potential-expressions-as-text-instead-of-atte%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Do a Find and Replace for = with '= and Replace All.
If your sheet has real formulas that you want to retain then you could limit the Find and Replace by selecting the cells that you want to change first. One way to do this would be to use Go To Special - hit F5 and specify Formulas with Error checkbox checked and the other checkboxes cleared.
answered Jan 9 at 21:50
Mark FitzgeraldMark Fitzgerald
3981211
Thank you Mark. A good answer to be sure. I was able to just click "Show Formulas" and save the resulting view to a csv. Then, when I ran a search for "#NAME?" in the resulting .csv file, it was nowhere to be found.
– Zack
Jan 10 at 13:22
add a comment |
Do a Find and Replace for = with '= and Replace All.
If your sheet has real formulas that you want to retain then you could limit the Find and Replace by selecting the cells that you want to change first. One way to do this would be to use Go To Special - hit F5 and specify Formulas with Error checkbox checked and the other checkboxes cleared.
answered Jan 9 at 21:50
Mark FitzgeraldMark Fitzgerald
3981211
Thank you Mark. A good answer to be sure. I was able to just click "Show Formulas" and save the resulting view to a csv. Then, when I ran a search for "#NAME?" in the resulting .csv file, it was nowhere to be found.
– Zack
Jan 10 at 13:22
add a comment |
Do a Find and Replace for = with '= and Replace All.
If your sheet has real formulas that you want to retain then you could limit the Find and Replace by selecting the cells that you want to change first. One way to do this would be to use Go To Special - hit F5 and specify Formulas with Error checkbox checked and the other checkboxes cleared.
answered Jan 9 at 21:50
Mark FitzgeraldMark Fitzgerald
3981211
Do a Find and Replace for = with '= and Replace All.
If your sheet has real formulas that you want to retain then you could limit the Find and Replace by selecting the cells that you want to change first. One way to do this would be to use Go To Special - hit F5 and specify Formulas with Error checkbox checked and the other checkboxes cleared.
answered Jan 9 at 21:50
Mark FitzgeraldMark Fitzgerald
3981211
answered Jan 9 at 21:50
Mark FitzgeraldMark Fitzgerald
3981211
answered Jan 9 at 21:50
Mark FitzgeraldMark Fitzgerald
3981211
answered Jan 9 at 21:50
Mark FitzgeraldMark Fitzgerald
3981211
3981211
Thank you Mark. A good answer to be sure. I was able to just click "Show Formulas" and save the resulting view to a csv. Then, when I ran a search for "#NAME?" in the resulting .csv file, it was nowhere to be found.
– Zack
Jan 10 at 13:22
add a comment |
Thank you Mark. A good answer to be sure. I was able to just click "Show Formulas" and save the resulting view to a csv. Then, when I ran a search for "#NAME?" in the resulting .csv file, it was nowhere to be found.
– Zack
Jan 10 at 13:22
Thank you Mark. A good answer to be sure. I was able to just click "Show Formulas" and save the resulting view to a csv. Then, when I ran a search for "#NAME?" in the resulting .csv file, it was nowhere to be found.
– Zack
Jan 10 at 13:22
Thank you Mark. A good answer to be sure. I was able to just click "Show Formulas" and save the resulting view to a csv. Then, when I ran a search for "#NAME?" in the resulting .csv file, it was nowhere to be found.
– Zack
Jan 10 at 13:22
add a comment |
Replace:
=-L
with:
="=-L"
etc.
answered Jan 9 at 22:48
Gary's StudentGary's Student
13.5k31730
add a comment |
Replace:
=-L
with:
="=-L"
etc.
answered Jan 9 at 22:48
Gary's StudentGary's Student
13.5k31730
add a comment |
Replace:
=-L
with:
="=-L"
etc.
answered Jan 9 at 22:48
Gary's StudentGary's Student
13.5k31730
Replace:
=-L
with:
="=-L"
etc.
answered Jan 9 at 22:48
Gary's StudentGary's Student
13.5k31730
answered Jan 9 at 22:48
Gary's StudentGary's Student
13.5k31730
answered Jan 9 at 22:48
Gary's StudentGary's Student
13.5k31730
answered Jan 9 at 22:48
Gary's StudentGary's Student
13.5k31730
13.5k31730
add a comment |
add a comment |
I asked this on another post and it got answered there. While prepending a single quote to the front of the string DOES do what I want, I didn't want to have to do this for every cell.
For my issue
Under Formulas in the Ribbon, Under (technically over) Formula Auditing, select Show Formulas. This applies to the entire worksheet.
Was enough to do what I want. Note, that this will effect the entire worksheet and not a single column. For my purposes, this was fine.
answered Jan 9 at 23:01
ZackZack
146116
Clever, but that won't work in this case. The OP needs the entire expression as a text string, presumably as input for other formulas. This solution would leave it as a formula, which in use, would output an error.
– fixer1234
Jan 9 at 23:32
I am the OP lol
– Zack
Jan 10 at 13:19
I didn't even catch that. :-) But if this solution is satisfactory for you, it means the question is a bit misleading. This is the solution to a different requirement, and doesn't really solve the issue of the error result which is a key element described in the question. That's why it never dawned on me to notice that you are the OP. At least you didn't accept this answer, which would have been really confusing for readers who come here with the problem described in the question. Well, this won't be the first time that someone discovered that what they originally thought (cont'd)
– fixer1234
Jan 10 at 13:48
were problem constraints turned out not to be. For the sake of other readers coming here for an answer, can you add a few sentences of explanation to the question and this answer? This answer will make more sense in the context of discovering a change in requirements. But for the question and answers to have the most value, it would be even better to add a new question that describes your current requirements and move this answer there. That way, there would be two different questions, both with appropriate and good answers.
– fixer1234
Jan 10 at 13:48
add a comment |
I asked this on another post and it got answered there. While prepending a single quote to the front of the string DOES do what I want, I didn't want to have to do this for every cell.
For my issue
Under Formulas in the Ribbon, Under (technically over) Formula Auditing, select Show Formulas. This applies to the entire worksheet.
Was enough to do what I want. Note, that this will effect the entire worksheet and not a single column. For my purposes, this was fine.
answered Jan 9 at 23:01
ZackZack
146116
Clever, but that won't work in this case. The OP needs the entire expression as a text string, presumably as input for other formulas. This solution would leave it as a formula, which in use, would output an error.
– fixer1234
Jan 9 at 23:32
I am the OP lol
– Zack
Jan 10 at 13:19
I didn't even catch that. :-) But if this solution is satisfactory for you, it means the question is a bit misleading. This is the solution to a different requirement, and doesn't really solve the issue of the error result which is a key element described in the question. That's why it never dawned on me to notice that you are the OP. At least you didn't accept this answer, which would have been really confusing for readers who come here with the problem described in the question. Well, this won't be the first time that someone discovered that what they originally thought (cont'd)
– fixer1234
Jan 10 at 13:48
were problem constraints turned out not to be. For the sake of other readers coming here for an answer, can you add a few sentences of explanation to the question and this answer? This answer will make more sense in the context of discovering a change in requirements. But for the question and answers to have the most value, it would be even better to add a new question that describes your current requirements and move this answer there. That way, there would be two different questions, both with appropriate and good answers.
– fixer1234
Jan 10 at 13:48
add a comment |
I asked this on another post and it got answered there. While prepending a single quote to the front of the string DOES do what I want, I didn't want to have to do this for every cell.
For my issue
Under Formulas in the Ribbon, Under (technically over) Formula Auditing, select Show Formulas. This applies to the entire worksheet.
Was enough to do what I want. Note, that this will effect the entire worksheet and not a single column. For my purposes, this was fine.
answered Jan 9 at 23:01
ZackZack
146116
I asked this on another post and it got answered there. While prepending a single quote to the front of the string DOES do what I want, I didn't want to have to do this for every cell.
For my issue
Under Formulas in the Ribbon, Under (technically over) Formula Auditing, select Show Formulas. This applies to the entire worksheet.
Was enough to do what I want. Note, that this will effect the entire worksheet and not a single column. For my purposes, this was fine.
answered Jan 9 at 23:01
ZackZack
146116
answered Jan 9 at 23:01
ZackZack
146116
answered Jan 9 at 23:01
ZackZack
146116
answered Jan 9 at 23:01
ZackZack
146116
146116
Clever, but that won't work in this case. The OP needs the entire expression as a text string, presumably as input for other formulas. This solution would leave it as a formula, which in use, would output an error.
– fixer1234
Jan 9 at 23:32
I am the OP lol
– Zack
Jan 10 at 13:19
I didn't even catch that. :-) But if this solution is satisfactory for you, it means the question is a bit misleading. This is the solution to a different requirement, and doesn't really solve the issue of the error result which is a key element described in the question. That's why it never dawned on me to notice that you are the OP. At least you didn't accept this answer, which would have been really confusing for readers who come here with the problem described in the question. Well, this won't be the first time that someone discovered that what they originally thought (cont'd)
– fixer1234
Jan 10 at 13:48
were problem constraints turned out not to be. For the sake of other readers coming here for an answer, can you add a few sentences of explanation to the question and this answer? This answer will make more sense in the context of discovering a change in requirements. But for the question and answers to have the most value, it would be even better to add a new question that describes your current requirements and move this answer there. That way, there would be two different questions, both with appropriate and good answers.
– fixer1234
Jan 10 at 13:48
add a comment |
Clever, but that won't work in this case. The OP needs the entire expression as a text string, presumably as input for other formulas. This solution would leave it as a formula, which in use, would output an error.
– fixer1234
Jan 9 at 23:32
I am the OP lol
– Zack
Jan 10 at 13:19
I didn't even catch that. :-) But if this solution is satisfactory for you, it means the question is a bit misleading. This is the solution to a different requirement, and doesn't really solve the issue of the error result which is a key element described in the question. That's why it never dawned on me to notice that you are the OP. At least you didn't accept this answer, which would have been really confusing for readers who come here with the problem described in the question. Well, this won't be the first time that someone discovered that what they originally thought (cont'd)
– fixer1234
Jan 10 at 13:48
were problem constraints turned out not to be. For the sake of other readers coming here for an answer, can you add a few sentences of explanation to the question and this answer? This answer will make more sense in the context of discovering a change in requirements. But for the question and answers to have the most value, it would be even better to add a new question that describes your current requirements and move this answer there. That way, there would be two different questions, both with appropriate and good answers.
– fixer1234
Jan 10 at 13:48
Clever, but that won't work in this case. The OP needs the entire expression as a text string, presumably as input for other formulas. This solution would leave it as a formula, which in use, would output an error.
– fixer1234
Jan 9 at 23:32
Clever, but that won't work in this case. The OP needs the entire expression as a text string, presumably as input for other formulas. This solution would leave it as a formula, which in use, would output an error.
– fixer1234
Jan 9 at 23:32
I am the OP lol
– Zack
Jan 10 at 13:19
I am the OP lol
– Zack
Jan 10 at 13:19
I didn't even catch that. :-) But if this solution is satisfactory for you, it means the question is a bit misleading. This is the solution to a different requirement, and doesn't really solve the issue of the error result which is a key element described in the question. That's why it never dawned on me to notice that you are the OP. At least you didn't accept this answer, which would have been really confusing for readers who come here with the problem described in the question. Well, this won't be the first time that someone discovered that what they originally thought (cont'd)
– fixer1234
Jan 10 at 13:48
I didn't even catch that. :-) But if this solution is satisfactory for you, it means the question is a bit misleading. This is the solution to a different requirement, and doesn't really solve the issue of the error result which is a key element described in the question. That's why it never dawned on me to notice that you are the OP. At least you didn't accept this answer, which would have been really confusing for readers who come here with the problem described in the question. Well, this won't be the first time that someone discovered that what they originally thought (cont'd)
– fixer1234
Jan 10 at 13:48
were problem constraints turned out not to be. For the sake of other readers coming here for an answer, can you add a few sentences of explanation to the question and this answer? This answer will make more sense in the context of discovering a change in requirements. But for the question and answers to have the most value, it would be even better to add a new question that describes your current requirements and move this answer there. That way, there would be two different questions, both with appropriate and good answers.
– fixer1234
Jan 10 at 13:48
were problem constraints turned out not to be. For the sake of other readers coming here for an answer, can you add a few sentences of explanation to the question and this answer? This answer will make more sense in the context of discovering a change in requirements. But for the question and answers to have the most value, it would be even better to add a new question that describes your current requirements and move this answer there. That way, there would be two different questions, both with appropriate and good answers.
– fixer1234
Jan 10 at 13:48
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%2f1392433%2fhow-do-i-force-excel-to-display-potential-expressions-as-text-instead-of-atte%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
Add a
'to the start of the cell.– DavidPostill♦
Jan 9 at 20:43
Is there a way to do this without going through each cell and prepending a single quote?
– Zack
Jan 9 at 20:47
No idea .......
– DavidPostill♦
Jan 9 at 20:49