Contains a Formula or Contains Text

[Scott in Phoenix]

Hi!

Love the new board. This BB client is awesome - used it before. I like!

QUESTION:
How can I tell if a cell contains a formula or if it contains text? This is driving me NUTS!

Any functions I try all look at the VALUE of the cell, rather than the CONTENTS of the cell. I need to be able to differentiate between cells that contain text, and cells that have a formula that references the text.

I need to be able to do this via a formula or function, so that I can apply Conditional Formatting to the cells that contain the actual text.

Can anybody help??

Thanks in advance.

Scott

[Steve Hartman]

TOOLS > OPTIONS On the view tab check Window options formulas box

[Scott in Phoenix]

Thanks, Steve.

Yes, it's true this will do what I'm looking to do. But I really thought there was some kind of function that would do this for me! It would be a whole lot slicker to put a conditional format that BOLDS the text version and leaves the formula version unbold...

??

[IML]

still not exactly what your after, but you could highlight your sheet and select goto under the edit menu, select special and the constants radio dial. Now hit bold.

[Matanuska]

Try looking at the text string returned by the Formula property of the VBA Range object referring to the cell:

stringToLookAt = Range("A1").Formula

If cell A1 contains a formula the string returned will contain an "=". If it contains just text it won't.

[Scott in Phoenix]

Matanuska -

What you posted has to be done in VB, right? Not something I can put in the Conditional Formatting dialog box, is it?

Just double-checking...

Doesn't it seem like there should be a function that returns what the actual cell contains??

[Matanuska]

You can create a custom function in VBA very easily to do this:

Function CellType(cellRef As Range) As String

If Left(cellRef.Formula, 1) = "=" Then
CellType = "Formula"
Else
CellType = "TextOrNumber"
End If

End Function

[Paul-Johnson]

Another quick check is [ctrl]+ ~

This shows either the formula or the value depending on which is entered.

[Drew]

You could try Juan Pablo's tip (from "Tip of the Day" December 18, 2001

Select A1.
Go to Insert, Names, Define.
Put a name in it, like HasForm
in the refers to put:
=GET.CELL(48,A1)

Now, select the range you want to "check" for formulas. Select Conditional Formatting, change "Value is" to "Formula is" and put in this formula
=HasForm
Click on the Pattern button and select something that makes it easy for you to see the click OK.

HTH,
Drew

Hey guys!

Thanks for all the great suggestions. Tried the nifty idea using the named ranges, but that limits me to 7 non-contiguous cells for the name, so I'd hafta have lots of different names...

IML - almost missed your idea. I always forget about the cool stuff you can do with Go to > Special...

I'm still hoping to figure out some slick way to do this... But for now, I think IML's idea will suffice. Not quite as slick as what I hoped for, but it works!

Thanks again, everybody. And if anyone comes up with a SLICK way to do this, post it! (You might also drop me an email so I know to take a look. Prolly won't check again for a while...)

Regards,

Scott