Formula to find highest number in single cell

Topic

New Reply

Hello – How would I write a formula in excel to return a 5 (the highest number in the cell) from a single cell that contains the following…

Simple – 1
Freak – 5
Hi – 3
Mill : 3

Thank you for the help!

Reply | Quote

Replies

JHA,

This UDF {user defined function} will return the value but it assumes only single digit numbers!
Calling sequence. [noparse]=iFindHigh(B1)[/noparse]
where B1 is the cell containing the data. :cheers:
30637-FindHigh

Code:

Option Explicit

Public Function iFindHigh(vText As Variant) As Integer

  Dim iNum As Integer
  Dim iVal   As Integer
  
  On Error Resume Next
  iFindHigh = 0
  For iVal = 9 To 1 Step -1
    iNum = InStr(1, vText, Chr(48 + iVal))
    If iNum > 0 And iVal > iFindHigh Then iFindHigh = iVal
  Next iVal
  
End Function

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

wow :o:
fantastic

Reply | Quote

Same Results different flavor, but again only works for single digit numbers.
Function MaxNum(x)
Dim Z As Integer
Dim MyLen As Integer
Dim i As Integer
Z = 0
MyLen = Len(x)
For i = 1 To MyLen
If Val(Mid(x, i, 1)) > Z Then
Z = Val(Mid(x, i, 1))
End If
Next i
MaxNum = Z
End Function

Reply | Quote

For single digits:
=LOOKUP(1E+100,SEARCH({1,2,3,4,5,6,7,8,9},A1),{1,2,3,4,5,6,7,8,9})

Reply | Quote