Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    Last edited by RetiredGeek; 2012-04-18 at 14:37.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post
    wow
    fantastic

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    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. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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})
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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