Convert Formula into Text

[DQR]

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

 

[SkipVought]

Hi,

I don't know of such a built-function. Heres one that you could use...

Function xlAddress(rng As Range) As String
    xlAddress = rng.Formula
End Function

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DQR]

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

 

[SkipVought]

Sure.

Function xlAddress(rng As Range) As Byte
    If rng.Value = rng.Formula Then
        xlAddress = 1
    Else
        xlAddress = 2
    End If
End Function

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com