Forum Discussion
How to get Excel to treat values as text with no functions or cell links?
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.
11 Replies
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.
- I am using Office 365 for the PC.
- You could try to manually set the format for the column ( that shouldn't calculate formulas / remove the commencing zero in numbers) to TEXT:
https://support.microsoft.com/en-us/office/format-numbers-as-text-583160db-936b-4e52-bdff-6f1863518ba4
Please keep in mind that numbers can't be in this format if you want to use them for calculations (e.g. substraction, summary, etc)
HCStymie1
Before entering any other characters type ( ' ) or press spacebar. That makes the whole cell text and it will not go into formula mode.Why not simply use Word? Excel is about calculation whereas you seem to require formatted text.
- 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.
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.
- 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.
