0

I have data exported from a database that's in a CSV that I need to view and manipulate in Excel. The problem is that some of the values in the cells start with an equal sign ( = ) so Excel thinks this is a formula and will try to interpret it, which fails and I'm left with "#NAME?" as the value when I save the file again. I need to preserve the data exactly as it was, so I can't escape or encapsulate it as recommended here: How to deal with lines starting with '=' in Excel?

Is there any way to completely turn off formulas in Excel so that values starting with = are ignored? etc.

I know that one option is to use a text editor to convert the = into some other character which doesn't occur anywhere else in the data and then after re-exporting from Excel use the text editor again to convert all instances of that character back into = but I'm hoping to avoid that if possible.

Thank you.

Improve this question
4
  • 1
    you can set the column in which they are put to Text.
    – Scott Craner
    Commented Apr 29, 2021 at 18:32
  • 2
    Can you not escape it using ' at the start of the line?
    – spikey_richie
    Commented Apr 29, 2021 at 18:42
  • @spikey_richie I can, but when I export the data as a CSV the ' is there and as ' appear elsewhere in the data I'm not sure how to remove them after.
    – Topher Rhodes
    Commented Apr 29, 2021 at 20:02
  • @ScottCraner I tried to do that using the following steps: Click on the column heading to select entire column. Click Format > Cells. Click the Number tab. Select “Text” from the Category list. Click OK. But it did not work, I still have #NAME? in those cells (when click the cell I see the value in the entry field, but it's #NAME? when saved as CSV)
    – Topher Rhodes
    Commented Apr 29, 2021 at 20:04

1 Answer 1

Reset to default
3

The formula below might help depending on how wide spread the issue is.

enter image description here

Improve this answer
1
  • This worked in my case. Thank you!
    – Topher Rhodes
    Commented Apr 29, 2021 at 20:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .