0

I am working on a class project that involves loading cleaned up data into Power BI to generate business visualizations and dashboards. I am trying to copy over a table with formulas referencing cells in another sheet in that workbook with the numbers needed for the analytics.

I did a show formulas and then a Cmd+A to copy the contents and the needed formulas but when I paste it into the other workbook that is connected to the shared drive I am on they all paste in with a single quote. For example, instead of =Summary!D13, I am getting '=Summary!D13.

It is very tedious to go into each cell and remove the single quotes individually. I have tried formatting each of the columns and rows I want as General to avoid this, as I have seen on here that this has helped some people. Unfortunately, I am still getting the same issue. A Find/Replace doesn't seem to be working either.

Also, I am on a Mac. I have downloaded the needed files and created the template on Excel for Mac and I am pasting the table with the formulas I have referred to in Excel Online on a shared drive.

Peregrino69
  • 5,004
user1786329
  • 1

1 Answers1

0

You are apparently using a Mac, so there is always a huge chance something I say, coming from Windows, will not work, but this one ought to.

The apostrophe preceding the = seems like Excel's standard indicator of text that should be left justified in the cell. (^ is for centering and " is for right justifying content.)

I haven't the least clue as to WHY it is happening, but I or someone else might with more information. If the "why" is important to you as well.

As for SOLVING it, VBA has a simple solution. No need for a macro proper, just to use the Immediate Window (which is something you can ALWAYS do even when, "ahem..." not allowed to use macros.

For Windows versions, clicking Alt-F11 brings up VBA and the Immediate Window is usually showing if you've never used VBA and chosen to suppress it. If it isn't readily apparent, going to the VIEW tab in the menu and looking nearly halfway down will give you something to click to bring it up.

So, select the cells you need to fix, then bring up VBA and its Immediate Window, and type or paste into that little window the following line:

selection.value = selection.value

Press Enter and it should fix the problem post haste. It does in Windows, and seems there'd be nothing different in the Mac implementation that would affect it so...

This is also helpful for instances in which you have some material that is formatted as text, then you change the formatting to General or what-have-you, and Voila! Um, Voila!... no difference on the screen. Select the affected cells, run this command and all will be better. (Assuming this latency Excel has in this circumstance is the sole cause of the non-change... there are other reasons people just don't seem to notice... but if so.)

Again though, although it works for me in a Windows version, it may not in a Mac version. But if not, there might be some fairly easily findable correct idea on the internet to use instead.

By the way, this command does NOT do something obnoxious like change formulas to their current values. A formula like 'A1+A2 will become the working formula A1+A2. (On the other hand, it also doesn't do something wonderful like change formulas to their current values... depending upon whether that's obnoxious or wonderful in the circumstance. But that's easy enough a slight bit differently.)

Remember that Immediate Window. I keep a Word document with snippets like that I've found (no idea where I found the above, can't give the due credit) for when I need a quick "get past something without writing a full macro" solution. I've even got some spreadsheets in which I put the code line into a Named Range just so I can get the text to use by opening it and copying back out the text. It's handy. So many other uses as well.

Jeorje
  • 21
  • 2