Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Is there a function that will tell if a specific cell has a plain (manually entered) value or has a formula?

share|improve this question

4 Answers 4

up vote 5 down vote accepted

With this little snippet you can do that.

Code

function isFormula(startcol, startrow) {
  // prepare string
  var str = String.fromCharCode(64 + startcol) + startrow;

  // retrieve formula
  var fCell = SpreadsheetApp.getActiveSpreadsheet()
    .getRange(str).getFormula();

  // return false if empty else true 
  return fCell ? true : false;
}

Usage

enter image description here

In this answer, Tom Horwood found a way to circumvent the usage of quotes.

Example

Add the script under Tools>Script editor and press the bug button to activate the authentication.

I've created an example file for you: Check for Formula

share|improve this answer
    
I was wandering... Your function is actually getting a string which is the address of a cell isFormula2("A1"). so I tried to create a new version of it like this: function isFormula2(cell) { return (cell.getFormula())!=""; } and I expected it to be callable using isFormula2(A1) but this does not seem to work and when trying to debug it shows cell as undefined... any idea why is this and if things could be changed so it will work with f(A1) instead of f("A1") ? –  epeleg Jan 24 at 9:14
    
@epeleg See my revised answer. –  Jacob Jan Tuinstra Jan 24 at 19:26
    
:) so you changed it so that instead of getting the address it now gets a column and row. is here no syntax such that the call would just be isFormula(a1) ? (in MS excel it would be a function that gets a range parameter) –  epeleg Jan 27 at 9:37
    
@epeleg The closest is isFormula_1("A2:A2"), look at my example file. Custom functions work that way. Without the quotes, you will get the value. With quotes the string, that can be used as such, but can't be copied/pasted. The COLUMN and ROW solution is just a work-a-round (inevitably). –  Jacob Jan Tuinstra Apr 20 at 17:45
    
what would be the value that a custom function would get if you call it with isFormula(a1:a1)? and what about isFormula(a1:b2)? –  epeleg Apr 21 at 19:36

I think I just discovered an undocumented formula in the new Google Sheets. @Pnuts was saying the CELL formula didn't work (partially because it's only available in the new Google Sheets), so I copied the code into a new Google Sheet. An error was raised, telling me my (custom) formula accepts only one argument and not two:
enter image description here

Renaming the isFormula into something else, resulted in a working custom function. After this being the case, I created yet another Google Sheet and used the isFormula formula. The formula doesn't appear in the auto-complete, but it is working:
enter image description here

So, if the formula is available throughout the new Google Sheets, then this would be the best answer:

Formula

=isFormula(A1)

Note

Only available in the new Google Sheets. I've asked a few other people to confirm the new formula and it's confirmed. As it's undocumented, its implementation is unsure and it might disappear. The ARRAYFORMULA doesn't work when used with this new formula.

Example

I've created an example file for you: isFormula

share|improve this answer
    
nice. looks like Google are adopting my recommendation :) But how do I switch to the "New Google Sheets" ? –  epeleg Jul 9 at 13:12
    
interesting. it works for my gmail account but not in my google apps account... –  epeleg Jul 9 at 13:16

It looks like there is a hidden built-in function ISFORMULA() from Google. I realized when I deleted my own function and was still working. I tried it in a new spreadsheet and still works. No cache issue.

share|improve this answer

@Jacob has provided an (the?) answer and this has been accepted but some 'comments' that might not fit as such:

With reference to =TYPE The function cannot determine whether or not a cell or range of cells is using a formula implies to me there is no such 'built-in' function - or hopefully it would have been mentioned there.

=CELL does not suit.

Find and replace however might sometimes be adequate, with Also search within formulae checked. This will (a) display the formulae and (b) allow stepping through them with Find if the search term is = (though this might give rise to 'false positives' - ie appear in a cell without a formula).

share|improve this answer
1  
This might make formulae searchable, but not usable as in a function. –  Jacob Jan Tuinstra Apr 19 at 11:38
    
Just to clarify - what you two are saying that I can not have a function that gets called like this: isFormula(a1)? and have it receive a range object? what about if i agree to call it with isFormula(a1:a1) can that work? –  epeleg Apr 20 at 6:11
    
@epeleg I was not saying =isformula(a1) is invalid - just considered other alternatives. Yes, it is valid, and isFormula(a1:a1) is also (but gets converted anyway). –  pnuts Jul 9 at 13:09
    
but what is the type if the parameter I will get in such a function? can I have a custom function called sizeOf(range) that will return the number of cells in that range? (and again - gets called as sizeOf(a1:b2) and not sizeOf("a1:b2") or any other syntax ? –  epeleg Jul 9 at 13:21
    
I don't understand "but what is the type if the parameter I will get in such a function?" =isformula(a1) returns either TRUE or FALSE. –  pnuts Jul 9 at 13:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.