If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Function to determine if a cell contains a formula
 Author Name Remember Me? Password
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Function to determine if a cell contains a formula

 Thread Tools Display Modes
#1
March 25th 10, 05:17 PM posted to microsoft.public.excel.misc
 DoubleZ external usenet poster Posts: 42
Function to determine if a cell contains a formula

In excel 2007, is there a formula to determine whether or not a cell contains
a formula? Basically I want to do and If/Then function that will return a
certain answer if a cell contains a formula and will return a different
answer if the cell only contains text (that was manually entered in).

Thank you.
DoubleZ
 Ads
#2
March 25th 10, 05:35 PM posted to microsoft.public.excel.misc
 Mike H external usenet poster Posts: 11,501
Function to determine if a cell contains a formula

Hi,

How about a UDF. Alt + F11 to open VB editor. Right click 'this workbook'
and insert module and paste the code in

Call with

=isformula(A1)

Function IsFormula(cel As Range) As String
If cel.HasFormula Then
IsFormula = "It's a formula"
Else
IsFormula = "It's not a formula"
End If
End Function
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

"DoubleZ" wrote:

> In excel 2007, is there a formula to determine whether or not a cell contains
> a formula? Basically I want to do and If/Then function that will return a
> certain answer if a cell contains a formula and will return a different
> answer if the cell only contains text (that was manually entered in).
>
> Thank you.
> DoubleZ

#3
March 25th 10, 05:43 PM posted to microsoft.public.excel.misc
 Luke M[_4_] external usenet poster Posts: 457
Function to determine if a cell contains a formula

Unfortunately, no. You could use a short UDF to determine this though. To
install the UDF, open the VBE (Alt+F11) and goto Insert, module. Paste the
following in:

'==========
Function IsFormula(r As Range) As Boolean
IsFormula = Left(r.Formula, 1) = "="
End Function
'==========

Close the VBE, and back in your workbook, you can simply use the formula
=IsFormula(A1)
to check if a cell contains a formula or not.

--
Best Regards,

Luke M
"DoubleZ" > wrote in message
...
> In excel 2007, is there a formula to determine whether or not a cell
> contains
> a formula? Basically I want to do and If/Then function that will return a
> certain answer if a cell contains a formula and will return a different
> answer if the cell only contains text (that was manually entered in).
>
> Thank you.
> DoubleZ

#4
March 25th 10, 05:59 PM posted to microsoft.public.excel.misc
 Chip Pearson external usenet poster Posts: 7,247
Function to determine if a cell contains a formula

For many years, the documentation for the CELL function stated that
you could use it to determine whether a cell has a formula, but it
never worked. So they changed the documentation.

You have to use a VBA function:

Public Function HasFormula(R As Range) As Boolean
HasFormula = R(1,1).HasFormula
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

On Thu, 25 Mar 2010 10:17:03 -0700, DoubleZ
> wrote:

>In excel 2007, is there a formula to determine whether or not a cell contains
>a formula? Basically I want to do and If/Then function that will return a
>certain answer if a cell contains a formula and will return a different
>answer if the cell only contains text (that was manually entered in).
>
>Thank you.
>DoubleZ

#5
March 25th 10, 06:08 PM posted to microsoft.public.excel.misc
 Luke M[_4_] external usenet poster Posts: 457
Function to determine if a cell contains a formula

Borrowing from Mike's post...

Function IsFormula(r As Range) As Boolean
IsFormula = r.HasFormula
End Function

--
Best Regards,

Luke M
"Luke M" > wrote in message
...
> Unfortunately, no. You could use a short UDF to determine this though. To
> install the UDF, open the VBE (Alt+F11) and goto Insert, module. Paste the
> following in:
>
> '==========
> Function IsFormula(r As Range) As Boolean
> IsFormula = Left(r.Formula, 1) = "="
> End Function
> '==========
>
> Close the VBE, and back in your workbook, you can simply use the formula
> =IsFormula(A1)
> to check if a cell contains a formula or not.
>
> --
> Best Regards,
>
> Luke M
> "DoubleZ" > wrote in message
> ...
>> In excel 2007, is there a formula to determine whether or not a cell
>> contains
>> a formula? Basically I want to do and If/Then function that will return
>> a
>> certain answer if a cell contains a formula and will return a different
>> answer if the cell only contains text (that was manually entered in).
>>
>> Thank you.
>> DoubleZ

>
>

#6
March 25th 10, 06:27 PM posted to microsoft.public.excel.misc
 DoubleZ external usenet poster Posts: 42
Function to determine if a cell contains a formula

Thank you Mike and Luke! I am surprised to hear that Excel does not have
that functionality built in, but at least it isn't a difficult UDF.

Thanks again.
DoubleZ

"Luke M" wrote:

> Unfortunately, no. You could use a short UDF to determine this though. To
> install the UDF, open the VBE (Alt+F11) and goto Insert, module. Paste the
> following in:
>
> '==========
> Function IsFormula(r As Range) As Boolean
> IsFormula = Left(r.Formula, 1) = "="
> End Function
> '==========
>
> Close the VBE, and back in your workbook, you can simply use the formula
> =IsFormula(A1)
> to check if a cell contains a formula or not.
>
> --
> Best Regards,
>
> Luke M
> "DoubleZ" > wrote in message
> ...
> > In excel 2007, is there a formula to determine whether or not a cell
> > contains
> > a formula? Basically I want to do and If/Then function that will return a
> > certain answer if a cell contains a formula and will return a different
> > answer if the cell only contains text (that was manually entered in).
> >
> > Thank you.
> > DoubleZ

>
>
> .
>

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Function to determine whether cell contains a formula? Don Excel Worksheet Functions 4 March 2nd 07 04:11 AM Value of cell to determine range in MAX Function [email protected] Excel Worksheet Functions 8 February 18th 06 06:34 PM Is there a way to determine which cell the function =min() used? Justin Excel Worksheet Functions 6 October 16th 05 01:14 PM Function to determine if any cell in a range is contained in a given cell [email protected] Excel Worksheet Functions 3 February 7th 05 04:19 PM Is there a function to determine whether a cell contains a formul. Christo Kriel Excel Worksheet Functions 1 November 20th 04 06:54 PM

All times are GMT +1. The time now is 01:45 PM.

 - Contact Us - ExcelBanter forum home - FAQ - Links - Privacy Statement - Top

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2015, Jelsoft Enterprises Ltd.
Copyright ©2004-2015 ExcelBanter.
The comments are property of their posters.