Contains a Formula or Contains Text
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Contains a Formula or Contains Text

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Arizona
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Arizona
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...

    ??

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Dallas, Texas
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    New Member
    Join Date
    Mar 2002
    Location
    Arizona
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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??

  7. #7
    New Member
    Join Date
    Feb 2002
    Location
    Dallas, Texas
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    New Member
    Join Date
    Feb 2002
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Another quick check is [ctrl]+ ~

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

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Boston, Mass
    Posts
    182
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Guest

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com