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

 

[sandy666]

what if "34joe50doe" ? it should be treated as 3450 ?

 

[koksalcanberk]

what if "34joe50doe" ? it should be treated as 3450 ?

Thank you for your reply.

No, it should be treated as is.

 

[sandy666]

so FALSE ?
34joe 3500doe = TRUE
any representative example?

 

[koksalcanberk]

so FALSE ?
34joe 3500doe = TRUE
any representative example?

"34joe50doe" = FALSE
"34joe3550doe" TRUE

 

[mikerickson]

Is the numeral in a consistant place in the cell? Always after a fixed number of characters or after a specific character, like - in the OP?
Does the numeral always have the same number of characters?

 

[sandy666]

ok, thanks for the clarify

 

[koksalcanberk]

Is the numeral in a consistant place in the cell? Always after a fixed number of characters or after a specific character, like - in the OP?
Does the numeral always have the same number of characters?

Thank you for your response.

Unfortunately, the answer the both of your questions is "no"...

 

[Peter_SSs]

Welcome to the MrExcel board!

You could consider using a user-defined function. Here are two options. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

The functions have an optional second argument if you wanted to look for a different sized number in the string - see rows 9 & 10.

Function GreaterThan(s As String, Optional lower As Long = 2500) As Boolean
  Dim RX As Object, M As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\d{" & Len(lower) & "}"
  For Each M In RX.Execute(s)
    If Val(M) > lower Then
      GreaterThan = True
      Exit Function
    End If
  Next M
End Function


Function Greater_Than(s As String, Optional lower As Long = 2500) As Boolean
  Dim i As Long, lng As Long
  
  lng = Len(lower)
  For i = 1 To Len(s) - lng + 1
    If Mid(s, i, lng) Like String(lng, "#") Then
      If Val(Mid(s, i, lng)) > lower Then
        Greater_Than = True
        Exit Function
      End If
    End If
  Next i
End Function

koksalcanberk 2020-05-20 1.xlsm
	A	B	C
1	John-2504-Doe	TRUE	TRUE
2	John-2010-Doe	FALSE	FALSE
3	34joe50doe	FALSE	FALSE
4	34joe3550	TRUE	TRUE
5	45abc 1111-3456	TRUE	TRUE
6	45abc 1111-2222	FALSE	FALSE
7
8
9	abc6789	FALSE	FALSE
10	abc6790	TRUE	TRUE
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=GreaterThan(A1)
C1:C6	C1	=Greater_Than(A1)
B9:B10	B9	=GreaterThan(A9,6789)
C9:C10	C9	=Greater_Than(A9,6789)

 

[koksalcanberk]

Welcome to the MrExcel board!

You could consider using a user-defined function. Here are two options. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

The functions have an optional second argument if you wanted to look for a different sized number in the string - see rows 9 & 10.

Function GreaterThan(s As String, Optional lower As Long = 2500) As Boolean
  Dim RX As Object, M As Object

  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\d{" & Len(lower) & "}"
  For Each M In RX.Execute(s)
    If Val(M) > lower Then
      GreaterThan = True
      Exit Function
    End If
  Next M
End Function


Function Greater_Than(s As String, Optional lower As Long = 2500) As Boolean
  Dim i As Long, lng As Long

  lng = Len(lower)
  For i = 1 To Len(s) - lng + 1
    If Mid(s, i, lng) Like String(lng, "#") Then
      If Val(Mid(s, i, lng)) > lower Then
        Greater_Than = True
        Exit Function
      End If
    End If
  Next i
End Function

koksalcanberk 2020-05-20 1.xlsm
	A	B	C
1	John-2504-Doe	TRUE	TRUE
2	John-2010-Doe	FALSE	FALSE
3	34joe50doe	FALSE	FALSE
4	34joe3550	TRUE	TRUE
5	45abc 1111-3456	TRUE	TRUE
6	45abc 1111-2222	FALSE	FALSE
7
8
9	abc6789	FALSE	FALSE
10	abc6790	TRUE	TRUE
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=GreaterThan(A1)
C1:C6	C1	=Greater_Than(A1)
B9:B10	B9	=GreaterThan(A9,6789)
C9:C10	C9	=Greater_Than(A9,6789)

First formula did a good job but failed to detect all the cells. But the second formula worked like charm! Thank you so much!!