How to get Excel to treat values as text with no functions or cell links?

Occasional Contributor

I have text in an Excel spreadsheet that has @ and = signs. By default, it thinks I want to link a cell into a function whenever I try to copy or update text in a cell. Is there a way to turn off this behavior for a file?

For example I may have text like: @RE_Somthing._RE_SomethingElse.ABDC = 'EFGH'

Which will be a calculation in a different program, but not in Excel where it is being edited before import. When I try to do anything within the cell, the next cell I click on it tries to add that cell number into the equation.  I don't want that. I already changed the cells category to Text. Putting a  single quote ' at the beginning of the text is not wanted since I don't want it to end up in the other software that will actually use the calculation.

9 Replies

@HCStymie1 

An apostrophe ' at the beginning of a cell instructs Excel to treat the value as text. In general, the apostrophe won't be included when you copy or export the data,

As an alternative, set the number format of the relevant cells to Text before entering data.

Why not simply use Word? Excel is about calculation whereas you seem to require formatted text.

The apostrophe is not an option since there are several columns and over 600 rows of data. Having to put an apostrophe in every row I touch is less than ideal. Plus, I would like to use it for a different Excel document also which is full of functions and linked cells or ranges. In that case I would also like to turn off Excel trying to link a cell every time I try to copy text from a cell.
The data is actually in tabular form for the Excel files I am working with. For the more complicated ones I do copy to a text editor and paste the updated text into excel, but even then it tries to link to the next cell I click on which is annoying. Plus the issue also happens with some other Excel documents that I am just trying to copy text from and don't want to update anything.
Have you tried switching the number format of an entire sheet to TEXT?
Bear in mind that no formula will work on the sheet and all apparent numbers are actually text characters.

@HCStymie1 
Before entering any other characters type ( ' ) or press spacebar. That makes the whole cell text and it will not go into formula mode.

Yes, I have tried that. But it does not actually work that way. It did allow me to switch cells without stopping me due to an invalid calculation though. Functions are still treated as functions and it will still try to link the cells on me. Setting the format to text just allows you to do things like enter 01 into the cell and have it display as text "01" and not be treated as a number and display 1.

@HCStymie1 Are you by any chance using Excel for the Mac? In May 2020, a feature called Enable Click to Add was introduced and it behaves exactly as you describe. Start a formula and click on cells. Excel will automatically insert the clicked cell reference into you formula proceeded by a + sign. Extremely irritating. Switch it of under Excel, Preferences, Edit. It's poorly documented on-line and it doesn't seem to exist in the Windows version. Here is what I did find in the MS support pages. It's the feature at the bottom of the picture.

 

Screenshot 2022-03-24 at 07.04.03.png

I am using Office 365 for the PC.