Contains a Formula or Contains Text

Scott in Phoenix

New Member
Joined
Mar 5, 2002
Messages
13
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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...

??
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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??
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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