Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Convert Formula into Text

Status
Not open for further replies.

DQR

Technical User
Dec 18, 2002
30
GB

Does there exist an Excel function which will transform a formula into a text string?

For example, if cell B2 contains the formula =B3+1, and I want a text version of this in cell A1, then I want to write '=XXX(B2)' into cell A1, where XXX is the name of this hypothetical function, and this will deliver the result "=B3+1" (i.e. as a text string).

If there isn't such a function, is there a function which will deliver one value if cell B2 contains a number, and a different value if it contains a formula? The closest I can get is the TYPE function, but this doesn't quite do what I want, as it returns a 1 if cell B2 contains either a number OR a formula which returns a number.

Many thanks in advance for any help on this,
DQR
 
Hi,

I don't know of such a built-function. Heres one that you could use...
Code:
Function xlAddress(rng As Range) As String
    xlAddress = rng.Formula
End Function
Hope this helps :)

Skip,
Skip@TheOfficeExperts.com
 
Cheers Skip - I'll try it out. I'm not very familiar with VBA programming, so forgive me if this is a silly question: once it's written, can this function be called in the same way you might call one of the standard built-in functions, e.g. you type "=xlAddress(A2)" into cell A1 if you want A1 to contain the text version of the formula in cell A2? Or can the function only be run as part of a macro?

As an aside, can you think of an easy way to tackle the alternative problem I outlined, e.g. writing a function which will deliver a 1 if the argument is a value, and 2 if it's a formula?

Best regards and many thanks,
DQR
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top