remove a ' to convert a comment to a formula in excel

[lsmithop]

I routinely stick a ' in front of a formula to stop Excel from complaining/editing my text while I edit it to be the formula I want. Then I remove the ' to convert the cell back to a live forumula.

Well, today I needed to edit 1500 cells to contain the formulas I needed, so i edited all my = to be '=, adjusted all my formulas, and now I cannot figure out how to use Excel's Replace (or even Find!!) to convert them back. Oops!

If I search for '= (just apostrophe equals) I get "We couldn't find what you're looking for". I have tried varying all the options, tried using ^? to avoid ' in case that's some special character, etc.

I attached a trivial case which shows the search failing...

Can someone save my 1500 formulas? How can I rescue them?

Thanks!

Len

[#2 · Accepted solution]

Hi @lsmithop Select the range of cells and use Text to Columns on the Data tab. Select nothing in the wizard, just click Finish and it should convert them back to formulas.

[#3]

Whaooo... That's cool trick!

[#4]

Here is also my solution in addition to the wizard solution provided by Alan

1. Select all the values in column C and press CTRL + C top copy
2. Select all the values in column A such as '=C1, '=C2 etc
3. In the Paste dropdown, Paste Link (N)

You get the same result that Text to Column delivered...

[#5]

@lsmithop 

May I ask an after-the-fact question? No real need to answer since you've gotten a resolution, but you piqued my curiosity.

You intrigue me with your situation: I can't help but wonder WHY you were editing 1,500 formulas. The only hypothesis I could come up with was that you had some variable in there that had to be changed. That is to say, you were changing some hard-coded number from XX to YY. If that is the case (was the case), I would want to make sure you knew about methods to avoid hard-coding numbers (numbers such as, let's say, sales tax percentages) into formulas. It's always better to have a cell (or a table) somewhere that you can refer to for numbers like that, numbers that may not change often but when they do require changes through all 1,500 cells that contain them.

So if that is the case, a cell that contains the value 0.0875, say, could receive the range name "SalesTaxPct" and then any formulas that refer to it to calculate something would say =A1*SalesTaxPct rather than =A1*0.0875.

Forgive me if you know all that already; as I said, I couldn't think of another reason to have to change the formula in 1,500 cells....

[#6]

@Alan MurrayHi Alan -- In case you are not old enough to remember Xerox' ad with Brother Dominic, here's a link: https://www.youtube.com/watch?v=LAt-lB9JIqw

That's how I feel about your solution! It's like you reached into Excel and said "This is what Len needs..."

Thank you 1,500 times!!

Len

[#7]

@mathetes--

OK, here's your answer, if you care enough to follow my clumsy explanation.

I have a "master" worksheet which has about 70 columns -- raw data, formulas, derived data, etc. It's too wide (and too much detail) to print for our users. So I made a second worksheet, which uses "stacked" columns (3 lines of report info/club member) to provide all important info on each member:

Member Name     Address                       Phones                   Emails   

John Smith           123 Main St.                H: 123-456-7890    John@home.com

                             Anytown, MD 11111   W: 123-543-2109   John@work.com

                                                                 C:  234-555-1212   John@ourclub.org

Each of those reporting cells (in worksheet 2) refers to a cell in a different column but the same row (that member's row) in the master data worksheet (#1). But when I copy the above 3-line report block in worksheet 2, to make additional report blocks, the member row references get incremented incorrectly (to N+4, instead of N+1). So the "solution" I hit upon (using the tools I know) was to put a ' in front of all the = in the report block. and change the row numbers to ^^ (thus turning off the row reference increments altogether). Then I copied my commented report block 9 times, changed the ^^ to **1, ^^2, ^^3, ..., and ^^0 in the ten blocks that I had. Then I duplicated those ten blocks ten times (I needed 110 rows in the report), and then I selected successive blocks of 10 rows, and changed the ^^ to null, 1, 3, 3, and up to 10. Thus, 20 edits corrected 100 rows. Since each block contained 15 cell references, and I had 110 blocks, I now had 1650 correct cell references, but I couldn't remove the bleeping ' until Alan shared his amazing knowledge.

I am sure there is a better way to have done this, but just like "the best camera is the one you have with you", my rule is "the best solution is the one you know how to do" (including know to ask the Excel forum community when you hit a brick wall!).

Thanks to all of you!

Len

P.S. And yes, a named cell range is WAY better than a text constant!! Thanks...

[#8]

ha ha happy to help @lsmithop 

[#9]

@lsmithop 

On the one hand I could agree with such approach - it's always better to use what you know better. On the other hand, taking into account that manual transformations are always source of errors an usually time consuming, it could be worth to invest some time for the automation of routine operations.

I believe people here could suggest several variants (everything in Excel could be done by several ways) of such automation, from Power Query to different variants of formulas.

For example, far from optimal but relatively simple is variant with offset(), here you may just drag F1:G1 down 

I have no intention to ask you for shifting on another method, that's only to illustrate that relatively simple but more reliable solutions usually exist.

[#10]

@Sergei Baklan

Sergei - Wow, thanks for a great tip. I have used H/VLookup (which look sort of similar) for years, but have never even looked at offset(). Will test it tomorrow -- it looks better (and potentially more maintainable) than my approach. Thanks again!

[#11]

@lsmithop , please note I gave only mockup, such formulas depends on actual data structure.

[#12]

@lsmithop 

Thank you for taking the time to answer my curiosity-motivated question. Your answer is not at all what I had hypothesized.

And I'm glad to see that @Sergei Baklan gave you his usual  brilliant alternative way to meet the need you've been meeting the hard way.

Still another alternative--if the group you refer to as  "our users" is really only accessing/viewing the information, i.e., not entering updates or corrections--would be to develop a distinct output report that you update every week (or however frequently it's called for). You could invest the time to use MailMerge in Word, using your Excel database as the source, printing the result to PDF.......and it could be formatted nicely, etc., etc. Once such a MailMerge file has been created, it would be simplicity itself to run it on whatever frequency is needed, printing hard copy if needed, certainly PDF for "soft copy"

That way you maintain the database in Excel as it should be maintained (single row per member); you can add a column if some new category of information becomes essential, make a simple change in the MailMerge master, and be all done with it. I like this because it cleanly separates the Input end of things from the Output...