Find Formula

[kenthehead]

I need to find out whether a cell contains a formula or an entered text value and place the result in the adjacent cell

e.g
ABC Text
=A+B Formula
etc, etc

I would prefer the answer as a formula and not VBA code

Thanks in advance

Much appreciated

Cheers

Ken

 

[Peter_SSs]

Ken

Try this

1. Select cell B1.

2. Insert|Name|Define...|Insert names in workbook: HasFormula|Refers to: =GET.CELL(48,!A1)|OK

Now go to the cell immediately to the right of the cell you want to query and enter the formula as shown below.

Excel Workbook
	D	E
1	abd	FALSE
2	5	TRUE
3	5	FALSE
Identify formula cells

 

[kenthehead]

Peter

I have defined the name but all that happens when I use it is that it returns the text

=GET.CELL(48,!A1)

I look at the name define and it has quotes around the formula. I must have done something wrong?

Cheers

Ken

 

[konew1]

=CELL("type",A1)
This will tell you if it is value or text/label or blank, but a formula that returns text is still treated as a label

 

[Peter_SSs]

I have defined the name but all that happens when I use it is that it returns the text

=GET.CELL(48,!A1)

I look at the name define and it has quotes around the formula.

I'm not sure what has gone wrong there. All I can suggest for a start is to go in to Insert|Name|Define...|select HasFormula|Delete|Close then follow the steps in my previous post exactly, particularly making sure cell B1 is selected before defining the name.

Having done it again, if the same thing happens, see if you can edit the defined name to remove the quotes around the formula.

What version of Excel are you using?

 

[konew1]

Peter
Your solution works for me on XL2007 returning True/False that could be turned into "Text" or "Formula" as requested in OP.
=IF(HasFormula,"Formula","Text")
Can you explain how it works, because I can't understand.
Cheers

 

[Peter_SSs]

Peter
Your solution works for me on XL2007 returning True/False that could be turned into "Text" or "Formula" as requested in OP.
=IF(HasFormula,"Formula","Text")

Yes you are correct - that is what the OP asked for.

Can you explain how it works, because I can't understand.

Here is a good starting point

 

[konew1]

Thanks for that link - very interesting.
I am puzzled by the cell reference "!A1" can you point to help on that?
Thanks
I tested your solution on XL2003 and worked as expected.

 

[John Davis]

I need to find out whether a cell contains a formula or an entered text value and place the result in the adjacent cell

e.g
ABC Text
=A+B Formula
etc, etc

I would prefer the answer as a formula and not VBA code

Thanks in advance

Much appreciated

Cheers

Ken

If all you have is Formula and Text in your columns then another approach might be:

=ISNONTEXT(A1)

 

[Peter_SSs]

If all you have is Formula and Text in your columns then another approach might be:

=ISNONTEXT(A1)

John

To be honest, I don't remember ever seeing the ISNONTEXT() function before.

However, I don't think it is up to this job, at least in some circumstances. In the sheet below
C1 contains directly entered text
C2 contains a formula

ISNONTEXT doesn't distiguish between them.

Similar situation where numbers are involved. The function doesn't distinguish between the formula and the non-formula

Excel Workbook
	A	B	C	D
1			Tim Brown	FALSE
2	Tim	Brown	Tim Brown	FALSE
3
4			5	TRUE
5	2	3	5	TRUE
Test