Find Formula

K

kenthehead

New Member
Joined
Feb 26, 2004
Messages
21
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
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
DE
1abdFALSE
25TRUE
35FALSE
Identify formula cells
 
Upvote 0
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
 
Upvote 0
=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
 
Upvote 0
kenthehead said:
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.
Click to expand...
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?
 
Upvote 0
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
 
Upvote 0
Upvote 0
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.
 
Upvote 0
kenthehead said:
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
Click to expand...

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

=ISNONTEXT(A1)
 
Upvote 0
John Davis said:
If all you have is Formula and Text in your columns then another approach might be:

=ISNONTEXT(A1)
Click to expand...
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
ABCD
1Tim BrownFALSE
2TimBrownTim BrownFALSE
3
45TRUE
5235TRUE
Test
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
setting a range from Active cell to last cell when Active cell change columns
2
Replies
12
Views
443
jolivanes
jolivanes
T
  • Solved
Return values from a Range
2 3
Replies
29
Views
565
dreid1011
dreid1011
K
  • Question Question
Modify COUNTIF Formula to count inside parenthesis
Replies
7
Views
136
Fluff
Fluff
D
  • Solved
Formula to Return a Cell address
Replies
2
Views
142
Scott Huish
Scott Huish
N
  • Question Question
Vlook up to return text unless the cell contains a certain phrase in which case I want it to look and return another cell?
Replies
1
Views
278
Kaper
Kaper

Forum statistics

Threads
1,223,104
Messages
6,170,125
Members
452,303
Latest member
c4cstore

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top