# Thread: Formula to find highest number in single cell

1. ## Formula to find highest number in single cell

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!

2. JHA,

This UDF {user defined function} will return the value but it assumes only single digit numbers!
Calling sequence. =iFindHigh(B1)
where B1 is the cell containing the data.
FindHigh.PNG

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

3. wow
fantastic

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

5. 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})

#### Posting Permissions

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