0

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?

  • 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
2

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.

  • 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
0

Replace:

=-L

with:

="=-L"

etc.

0

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.

  • 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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.