If a text cell contains value greater than

[koksalcanberk]

Hello,

I want excel to return TRUE if a text cell contains value greater than 2500.

For example, it has to return TRUE for the cell with "John-2504-Doe" in it.

For the cell with "John-2010-Doe" in it, on the other hand, it has to return FALSE.

Is this possible to do?

Best,
C

 

[Peter_SSs]

First formula did a good job but failed to detect all the cells.

Could you give a couple of examples of what was in cells that it failed to detect?

But the second formula worked like charm! Thank you so much!!

Good news. You're welcome. Thanks for the follow-up.

 

[koksalcanberk]

Could you give a couple of examples of what was in cells that it failed to detect?

Good news. You're welcome. Thanks for the follow-up.

Sure thing!

I actually tried hard to notice a pattern, but failed to do so. But as much as I can see, when there's a "/" or "-" before OR after the value, the code fails to detect it. Having said that, when there's "/" or "-" before AND after the value, it does get detected. When the value takes place at the very end, such as "John-Doe-3450", it doesn't get detected either.

There are too many data though. So I'm not sure with any of these. Unfortunately my data set is confidential, so I'm not able to share it

 

[Peter_SSs]

as much as I can see, when there's a "/" or "-" before OR after the value, the code fails to detect it. Having said that, when there's "/" or "-" before AND after the value, it does get detected. When the value takes place at the very end, such as "John-Doe-3450", it doesn't get detected either.

Hmm, I don't know what is going on with your sheet then as both work for me for all those cases if I have understood correctly. These are what you are describing as failures for the first function aren't they?

koksalcanberk 2020-05-20 1.xlsm
	A	B	C
1	John/2504Doe	TRUE	TRUE
2	John2710/Doe	TRUE	TRUE
3	John-Doe-3450	TRUE	TRUE
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=GreaterThan(A1)
C1:C3	C1	=Greater_Than(A1)

 

[koksalcanberk]

Hmm, I don't know what is going on with your sheet then as both work for me for all those cases if I have understood correctly. These are what you are describing as failures for the first function aren't they?

koksalcanberk 2020-05-20 1.xlsm
	A	B	C
1	John/2504Doe	TRUE	TRUE
2	John2710/Doe	TRUE	TRUE
3	John-Doe-3450	TRUE	TRUE
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=GreaterThan(A1)
C1:C3	C1	=Greater_Than(A1)

I'm sorry, I just saw this Peter. Yes, that's actually what I wanted to do so, no idea what was going on with my sheet! Maybe I observed it wrong.

The reason I came back to this thread was because I wanted to ask how can I apply this VBA code into a document in Google Sheets and not in my local computer. Is this possible?

 

[Rick Rothstein]

I know I am late to this thread, but here is another UDF (user defined function ) that I think should also work...

Function GreaterThan(ByVal S As String, Optional Lower As Long = 2500) As Boolean
  Dim X As Long
  For X = 1 To Len(S)
    GreaterThan = Val(Mid(S, X)) > Lower
    If GreaterThan Then Exit For
  Next
End Function

 

[Peter_SSs]

that I think should also work...

Such data may not be possible but that would fail for at least one of the usual suspects:
John2054E2