find the highest letter/number combination

[brakbek]

I wish to have Excel 2000 look along a row of letters and return the
highest letter/number (latest in the alphabet) to a cell.
the range is like: A-B-C1-D3
i can find the highest letter, but i can not find the highest number.
i want excell to find the combination D3, does anyone know how to solve
this problem?

 

[RNC]

Will you have only a single letter in each cell? Is each letter upper
or lower case?
If the answer is yes to both questions, create a formula next to each
element of the row as follows:

assuming your row begins in D1 and ends at L1,
in D2 enter "=Code(D1)+right(D1, len(D1)-1)"
in C2 enter "=max(D2:L2)"
in B2 enter "=match(C2, D2:L2, 0")
in A2 enter "=index(D1:L1,1,B2)

That should retrieve the largest letter/number combo in A2

 

[jkend69315]

brakbek, here's an alternate solution. Copy this code and paste it
into a standard module in your workbook.

Function BIG(myRg As Range)
Dim Cell As Range, v1 As Double, v2 As Double
Dim i As Integer, ch As String, f As Double
v1 = 0
For Each Cell In myRg
If Len(Cell) > 0 Then
f = 100000
v2 = 0
For i = 1 To Len(Cell)
ch = UCase(Mid(Cell, i, 1))
v2 = v2 + (Asc(ch) * f)
f = f / 100
Next i
If v2 > v1 Then
v1 = v2
BIG = Cell
End If
End If
Next
End Function

To use it (say your range of cells is in C7:F7), type this into a cell
=BIG(C7:F7)

James

 

[brakbek]

(e-mail address removed) schreef:

james thank you for your fast response, I think this is exactly wat
what I need......what I can see, it works perfectly...

 

[jkend69315]

Glad to help. Thanks for letting me know it will work for you. James