Mar 23 2022 02:01 PM
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.
Mar 23 2022 02:50 PM
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.
Mar 23 2022 03:02 PM - edited Mar 23 2022 03:02 PM
Why not simply use Word? Excel is about calculation whereas you seem to require formatted text.
Mar 23 2022 03:21 PM
Mar 23 2022 03:25 PM
Mar 23 2022 03:36 PM
Mar 23 2022 03:43 PM
@HCStymie1
Before entering any other characters type ( ' ) or press spacebar. That makes the whole cell text and it will not go into formula mode.
Mar 23 2022 03:50 PM - edited Mar 28 2022 05:16 AM
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.
Mar 23 2022 11:27 PM
@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.
Mar 24 2022 11:34 AM
Jun 08 2022 06:06 AM
Jun 08 2022 06:27 AM
@Moriz_Kaufmann I did set the formatting to Text. Problem is things like equals signs and parentheses make Excel think you are trying to enter a formula or link to another cell if you click on another cell in the same or another Excel document. If I recall, I was able to put a single quote at the beginning of a line to tell Excel to treat the contents as text but forget if that also had limitations or not. Just didn't want those single quotes at the beginning of the text since I had to copy and paste everything back into the original program when done.