0

How can I detect if certain cells contains specific formula with specific text/symbols?

1. A formula which is =today(), or =date(), or =time()
2. A formula which starts with =(any alphabet letter)
3. A cell which starts with '
4. A formula which ends with any number

I use the formulas in conditional formatting.

Thank you.

  • 3
    FORMULATEXT will give you the text of a formula, you can then look for any string in it – cybernetic.nomad Oct 10 '18 at 21:13
  • Just an observation: p._phidot_ took the time to develop almost the entire solution for you. For the last piece, you apparently found what you were looking for somewhere else. Posting two other people's work as your own answer and accepting it is a little tacky. It isn't clear where you found José Augusto's answer, but a better approach would have been to either link to that answer in a comment or work with p._phidot_ to add or reference that in the answer with attribution. Then accept p._phidot_'s answer (which you could still do and delete your own). (cont'd) – fixer1234 Oct 28 '18 at 20:26
  • That would also award you both a little rep for the effort. – fixer1234 Oct 28 '18 at 20:27
  • Thanks for your comment. I don't mind about the rep and the little stuff. I don't know if I could get rep in this case (I can reward myself rep? If it were the case, I would have created multiple questions and posted multiple answers myself. A big loophole) – masterton Oct 29 '18 at 8:02
  • Initially I wanted to select both as the answers but the system didn't allow it. The aim is to spread knowledge and benefit everyone. I think this is most important (not the rep stuff). If I posted Q3 in the comment and selected that as the solution, it would be hard for people to notice it. Perhaps I shouldn't have shared my findings and just kept it silent. I could delete the answer if people think the accidental rep gain is a major concern. I don't mind the rep at all. Anyway I would ask p._phidot_ to update the answer. I could delete it if he prefers this way. Thank you. – masterton Oct 29 '18 at 8:09
1

Ans for 1, 2, n 4 :

1a ) =IF(FORMULATEXT(A1)="=TODAY()",1,0)

1b ) =IF(LEFT(FORMULATEXT(A1),5)="=TIME",1,0)

1c ) [ edit the 1b solution ]

2 ) =IF(AND(CODE(MID(FORMULATEXT(A1),2,1))>=65,CODE(MID(FORMULATEXT(A1),2,1))<=90),1,0)

[ you may refer the details of code() in the help file.. (: ]

4 ) =IF(AND(code(right(FORMULATEXT(A1),1))>=48,CODE(right(FORMULATEXT(A1),1))<=57),1,0)

Hope it helps.

note for Q3 :

a formula almost always starts with a "=" character.. it never starts with a characters.. unless you actually referring to a text value.. then you may use the if() + left() + edit the solution for Q2 .

Please share what you had tried and didn't work.. I'll help others to understand where you stuck at and help out. ( :

  • For Q3, sorry for the confusion. What I mean is the cell starts with the symbol apostrophe. It forces the input to be text only. Apostrophe is hidden in display. For example: '=1+1 – masterton Oct 11 '18 at 8:39
  • For Q4 it could be. For example, =today()+5. Thank you for your reply. – masterton Oct 11 '18 at 8:40
  • updated Q4 answer. btw, '=1+1 is a string / text .. not a formula (as mentioned in the question).. – p._phidot_ Oct 11 '18 at 19:49
  • after a moment of thinking.. I propose this as the solution for Q3 : =IF(LEFT(A1,1)="=",1,0) – p._phidot_ Oct 12 '18 at 13:28
  • 1
    Finally I found the answer for Q3. I combined yours with the answer for Q3 in one post. Thank you so much for your help. – masterton Oct 28 '18 at 6:34
1

Answer for Q3

Open VBA and create a macro:

Option Explicit
Function ZIsApostrophe(rng As Range) As Boolean
    ZIsApostrophe = rng.PrefixCharacter = "'"
End Function

Use the custom function ZIsApostrophe() to check if a cell starts with ' .

For example type ZIsApostrophe(A3) to check if the cell A3 starts with ' . It will return the value TRUE or FALSE as the result.

Credit: José Augusto


Answers for 1, 2, and 4

1a ) =IF(FORMULATEXT(A1)="=TODAY()",1,0)

1b ) =IF(LEFT(FORMULATEXT(A1),5)="=TIME",1,0)

1c ) [ edit the 1b solution ]

2 ) =IF(AND(CODE(MID(FORMULATEXT(A1),2,1))>=65,CODE(MID(FORMULATEXT(A1),2,1))<=90),1,0)

[ you may refer the details of code() in the help file.. (: ]

4 ) =IF(AND(code(right(FORMULATEXT(A1),1))>=48,CODE(right(FORMULATEXT(A1),1))<=57),1,0)

Credit: p.phidot

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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