Text to Formula
A formula to convert text to a formula; which is essentially the exact opposite of the FORMULATEXT function, or in a way, what the INDIRECT function does with cell references but with formulas.
5 comments

Roy commented
No, not cell comments.
Also, as an aside, can people get OFF this drone about volatile functions? Most spreadsheets are simply so small that it cannot matter in the least on a modern computer. (Not a poke at you Harlan Grove, just I hate seeing that endlessly, as if everyone writes only spreadsheet with 4,000,000 full column reference array formulas using 250 volatile functions in each one. These same people (on tip sites) then often go on to use equally rabid resource eating techniques, creating their own volatility, but that's ok, right, 'cause it's THEIR clever thing? Color me TIred on that subject.
I know a BUNCH of incell commenting techniques. But look on this site alone at all the people who just simply find them obnoxious and fairly close to worthless. This would let you comment to your heart's content, even "real looking" tabbed over from the end of the line looking comments if one used AltEnter freely, and they all go away, just like compiling, with that simple SUBSTITUTE() wrapped around the formula.
Though I have the feeling Excel's recent change from 3 lines in the formula editor to 5 lines presages some improvement on this to be announced in the relatively near future. Not "coming"... just announced. But someday. (They did not shout the 3 to now 5 line change all over the site or internet, so I think it is not to make life more pleasant for those who've been asking for literally that, but rather something larger.)
Not to mention how a function like this could be used to take a piece from here and a piece from there. Now you can take whole resulting values as pieces, of course, but there are many times a function uses material in a different form internally to a formula, and cannot use a formal result from the same functions. Set this kind of piece up as text so it can be literally any form needed, and you could overcome how Excel doing the above sometimes shortcircuits because the form it needs to start out swiftly becomes unusable to the next portion, while the pieces you set up could handle that form change OUTSIDE of the final formula so the whole formula works rather than chokes. (By internal processing result, I mean the kind of things you see with F9, not things flowing in their programming itself. Like an array being created intermediately, but it isn't {1,2,3} but rather {"1","2","3"} and works, but stays so pushing further on when it simply won't so your clever idea fails for a truly SAD reason.)

Siddarth Narasimhan commented
Sure, any Excel function can be done with VBA. But I find it somewhat annoying that a simple operation can only be executed with code. Also, I feel that Excel is somewhat incomplete by having a FORMULATEXT function but not a TEXTFORMULA function.

Harlan Grove commented
Not that difficult to implement as a userdefined function in VBA, wrapping VBA's Evaluate.
Problem is that it'd need to be a volatile function. There are usually better nonvolatile solutions.
As for those who want commented formulas, cell notes? As for formulas returning numeric values,
=(some_long_expression)+N("and now for a comment")

Roy commented
Good Lord, just realized this could make it possible to create coimmented formulas in an almost natural way.
You'd have to write the formula in a different cell, say on a hidden page/tab that follow the current page. Write it with comments in a multiline format with the comments "wrapped" by a fairly unique character before and after. Then in the cell where you want the formula, use FORMULATEXT() (then string functions, if needed at all) to take the cell's contents minus anything between those unique characters but largely using SUBSTITUTE() to remove them (and probably TRIM() as well to clean it up a tad) then wrap the result with this function to turn the result into a working formula.
Almost natural. For, as they used to say, some values of "almost."
But since you can't really write commented formulas now and this would see you editing the commented version, not the workhourse version in the cell...
(Probably you'd wriote the formula without commenting, copy and paste its content (F2, then copy it all) to its holding cell and break it into a multiline format that you'd comment. Then return to the original cell and strip the comments and make it a working formula.
So, natural commenting not funny bits stuck into "N("Insert comment here")" additions.
Chandoo has a simple 4 line UDF to bring cell comments into a cell. One could use such, but preferrably, may Excel would bring a partner function into existence that would do it for you, then you could put these into a cell comment, not the hidden sheet thought. Extract them with the partner function then continue as above. Or, create a new kind of comment to link to a cell for this precise purpose.

Roy commented
First time I've seen that annoying Search when typing the header line for an idea work.
Absolutely would be HANDY.
Using lookups to pick a formula to use, for instance. Usually a lookup searches for some value and returns some value related to it stored to its right in a table. Picture a table of values with formulas to use if one is the case. Lookup a value (or set of values) that conditions dictate and find to its right a formula to use.
That would HUGELY reduce complexity based on nested IF()'s. One can do a version of this now, but it is greatly limited and according to answers provided to questions on the internet, it looks like no one really does, in practice.
But say one has four conditions. The IF()'s will be nested four deep and use of IFERROR() is likely not possible even if it could have been useful. Maybe a couple of the conditions are more involved AND()ing or OR()ing possibilities. Instead of taking easily understood logic and forcing it into an arcane nested IF() structure with the formulas to use when the conditions resolve buiried in it, one could instead do a multiple condition lookup to match the conditions that exist that moment to a formula that is in plain sight in the formula table. Result achieved. Understanding possible when maintaining the spreadsheet over time. Yay.
It would also be easily possible to create dynamic formulas as INDIRECT() and its weirdnesses would not have to be used. Rather, one could use simple string functions to build the formula. Much easier to do and much easier to maintain as anything would go whereas presently there are limits due to such things as INDIRECT() not being able to access a closed file*). This would create a completely live formula that could access anything the typed version of it could. (Hence my "as anything would go" statement.)
* Yes, I know there are ways toi sometime force it to, using the Excel 4 macro command EVALUATE for example. But those ALL have severe limitations. For instance, XL4's EVALUATE requires a Named Range be created for it so it's a oneoff solution. Have 648 formulas you need to do that with? Well, that's 648 Named Ranges...
It could even be used inside formulas to add a better dynamic portion than currently available. Pieces could come from lookups, as above, or IF()'s, and make the larger formula it is part of easier to understand (therefore maintain).