Convert formula string to text

[dmcgoo]

Hi all

I am trying to convert the following cell formula into a text string so that I can use the "mid" formula to pick up some of the text.

='[20031102.xls]Day Perf Report'!$D$6

I have not had any luck in being able to do this - can anybody help?

Thanks muchly.

 

[Mat]

Well, you can use the MidFun() that you declare in a module in vba

Function MidFun(rg As Range, iStart As Integer, iNum As Integer) As String

Application.Volatile
MidFun = Mid(rg.Formula, iStart, iNum)

End Function

and use it:
B1= MIDFUN(A1,1,2)

just as the Mid function...

But I think there is surely a way without using a personnalised function in vba...

 

[parry]

Hi, I gave up on trying to do this all as a formula so theres a formula & macro combo solution.

Place these into your module...

Function ShowF(Rng As Range)
ShowF = Rng.Formula
End Function

Sub Convert2Formula()
Dim Rng As Range, c
Set Rng = Application.InputBox(prompt:="Select range to convert", _
Title:="Convert string to formula", Type:=8)

For Each c In Rng
c.Formula = c.Value
Next c

End Sub

Now use the ShowF formula with mid to determine the final string you need.
eg

A1 formula is =Sheet1!A1
to turn this into just A1 do this...
="=" & Mid(ShowF(A1),9,2)

Once you have all the formulas as you would like then run the Convert2Formula macro which changes this from a string into a formula. Note that the equal sign must be in the string but could be amended to auto include this if you want.

hth

 

[dmcgoo]

Thanks for your help guys! I will try these out and let you know if I have any more problems!

Cheers

DM

 

[DeonP]

Hi there

I have found a work around - Copy your formulas to a column next to the current formula. Then you highlight the new column and Find and replace the = with '
Your new column will then have the formula in text. Make your changes and then just Find and replace the ' with =

Hope this helps you.