extracting text from a cell formula

[none]

Hi All,

I would like to pull out some text from a cell formula, can anyone give me
an idea how to do it if it is possible. We have a pop quiz and the answers
are given like follows:
=IF(C8="Leo Sayer","Correct"," ")
I would like to be able to list all the answers

Cheers,

Ingrid

 

[Bob Phillips]

Ingrid,

Put the answers in a column far to the right, say BH, and hide the column
Then use

=IF(C8=BH8, "Correct","")

Not foolproof, but anyone who needs to cheat shouldn't be playing.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bernard Liengme]

Hi Ingrid,
Let's say the IF formula is in G8
1)Use VBA editor to Insert a module and add this function
Function showform(x As Range)
showform = x.Formula
End Function
2) In H8 enter =SHOWFORM(G8) and the formula is displayed
3) In I8 enter =MID(H8,FIND("""",H8)+1,FIND(",",H8)-2-FIND("""",H8))
This will extract the first piece of text in the formula

Hope I have not screwed up some teacher's quiz!

 

[none]

Thanks Bernard! Don't worry, it is just a music quiz between friends!!!