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]

euhm, Assuming the formula you gave is in cell A1

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

Then in another cell, say B1, you can use the mid function without converting in text...

=MID(B1,3,1)

it will give the third letter of the value in A1

you can put it directly in A1 too..

A1=MID('[20031102.xls]Day Perf Report'!$D$6,3,1)

 

[dmcgoo]

Hi mate

Thanks for the speedy reply however when I do as you have suggested the cell where I expect the "mid" result (B1) is blank. When I look into the formula (via function arguments) the result is "".

The format for both "A1" and "B1" is General.

 

[Mat]

But what is the value in A1? if the result of A1='[20031102.xls]Day Perf Report'!$D$6 is nothing, sure in B1 you'll have nothing.

 

[Mat]

Formulas:
A1: ="Test"
B1: =A1
C1: =MID(B1,3,1)

Results:
A1: Test
B1: Test
C1: s


I don't understand if you don't get this!

 

[dmcgoo]

Hi mate

A1 result is the number 7 as generated by cell D6 in worksheet 20031102.

Cheers

 

[Mat]

Then if you look for the 2nd and over characters , you won't get anything since there is only one character in it.

 

[dmcgoo]

I agree but I can see where I have not been clear enough.

In your example below, I want the mid function to return the value by looking at the formula and not the result.

From your post below I would want the mid function to look at the formula code and not the result in C1, ie by saying mid(b1,2,2) I want to the result to be text of "A1" from the formula code in B1 and not "es" from the formula result.

Is this any clearer? I want the mid function to return a result by looking at the actual code and not the formula output in b1.

 

[Mat]

Ohhh ok then I'll need to think a little more...

I know how to do it with vba, but with excel function.. I'll take a look at this!

 

[parry]

Hi dmcgoo, I could return the formula for you easy enough but converting that into a formula is proving quite difficult. I will have to think about this. Heres a UDF to find the formula. Paste into a module then use Showf as any formula eg =showf(a1).

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

I will come back to you on the hard part