Max-function with alphanumeric values?!?
 

Hi

I have a column with alphanumeric data. The max function works only with
numeric values. How to calculate the max-value of alphanumeric values?

Tom

Max-function with alphanumeric values?!?
 

Hi Tom,

Here is an easy way...please use code function like =CODE(A1), so you can
use MAX function.


--
Kind Regards
Colo
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Colo of 'The Road of The Cell Masters' :)

URL:http://www.interq.or.jp/sun/puremis/...astersLink.htm


/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/


"Tom" wrote in message
...
Hi

I have a column with alphanumeric data. The max function works only with
numeric values. How to calculate the max-value of alphanumeric values?

Tom

Max-function with alphanumeric values?!?
 

Coolio

I suppose that the Code-function fails... I have numbers like this:

01/001
01/002
01/003
02/001

Tom



"Colo" schrieb im Newsbeitrag ...
Hi Tom,

Here is an easy way...please use code function like =CODE(A1), so you can
use MAX function.


--
Kind Regards
Colo
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Colo of 'The Road of The Cell Masters' :)

URL:http://www.interq.or.jp/sun/puremis/...astersLink.htm


/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/


"Tom" wrote in message
...
Hi

I have a column with alphanumeric data. The max function works only with
numeric values. How to calculate the max-value of alphanumeric values?

Tom

Max-function with alphanumeric values?!?
 

What is the max in your sample data - does 02 take precedence over any 01 in
the first two places or is the max the highest of the last 3 digits.

Can a helper column be used next to this column?

--
Regards,
Tom Ogilvy

"Tom" wrote in message
...
Coolio

I suppose that the Code-function fails... I have numbers like this:

01/001
01/002
01/003
02/001

Tom



"Colo" schrieb im Newsbeitrag
...
Hi Tom,

Here is an easy way...please use code function like =CODE(A1), so you can
use MAX function.


--
Kind Regards
Colo
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Colo of 'The Road of The Cell Masters' :)

URL:http://www.interq.or.jp/sun/puremis/...astersLink.htm


/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/


"Tom" wrote in message
...
Hi

I have a column with alphanumeric data. The max function works only with
numeric values. How to calculate the max-value of alphanumeric values?

Tom

Max-function with alphanumeric values?!?
 

Hi Tom, Hmmm...we need more information. Which one is MAX in your example.

02 of 02/001 is MAX? or 003 of 01/003?

Anyway try something like this...(the Code-function must fails again.)

'-----------------CODE -------------------------------------------------

Sub Test()
MsgBox CustomMax([A1:A4], 1) 'Take value of the Left side
MsgBox CustomMax([A1:A4], 2) 'Take value of the Right side
End Sub


Function CustomMax(ByVal Target As Range, ByVal lngNum As Long) As String
Dim buf1, buf2()
Dim ret
Dim i As Long
buf1 = Target.Value
ReDim buf2(LBound(buf1) To UBound(buf1))
For i = LBound(buf2) To UBound(buf2)
buf2(i) = Val(Split(buf1(i, 1), "/")(lngNum - 1))
Next
ret = Application.Match(Application.WorksheetFunction.Ma x(buf2), buf2,
0)
If Not IsError(ret) Then
CustomMax = buf1(ret, 1)
Else
CustomMax = "Error"
End If
End Function

'----------------------------------------------------------
"Tom" wrote in message
...
Coolio

I suppose that the Code-function fails... I have numbers like this:

01/001
01/002
01/003
02/001

Tom



"Colo" schrieb im Newsbeitrag
...
Hi Tom,

Here is an easy way...please use code function like =CODE(A1), so you can
use MAX function.


--
Kind Regards
Colo
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Colo of 'The Road of The Cell Masters' :)

URL:http://www.interq.or.jp/sun/puremis/...astersLink.htm


/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/


"Tom" wrote in message
...
Hi

I have a column with alphanumeric data. The max function works only with
numeric values. How to calculate the max-value of alphanumeric values?

Tom

Max-function with alphanumeric values?!?
 

the max-value of the the following entries is 02/001

01/001
01/002
01/003
02/001

I can solve this with a customized function, but I can't believe that this
can't be solved with a worksheet function.

Tom



"Tom Ogilvy" schrieb im Newsbeitrag
...
What is the max in your sample data - does 02 take precedence over any 01
in
the first two places or is the max the highest of the last 3 digits.

Can a helper column be used next to this column?

--
Regards,
Tom Ogilvy

"Tom" wrote in message
...
Coolio

I suppose that the Code-function fails... I have numbers like this:

01/001
01/002
01/003
02/001

Tom



"Colo" schrieb im Newsbeitrag
...
Hi Tom,

Here is an easy way...please use code function like =CODE(A1), so you
can
use MAX function.


--
Kind Regards
Colo
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Colo of 'The Road of The Cell Masters' :)

URL:http://www.interq.or.jp/sun/puremis/...astersLink.htm


/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/


"Tom" wrote in message
...
Hi

I have a column with alphanumeric data. The max function works only
with
numeric values. How to calculate the max-value of alphanumeric values?

Tom

Max-function with alphanumeric values?!?
 

you could use a helper column (assume values in A, helper column B)

=SUMPRODUCT(CODE(MID(A1,{1,2,3,4,5,6},1)),10^{5,4, 3,2,1,0})

Drag fill down column B.

=Index(A1:A4,Match(Max(B1:B4),B1:B4,0),1)

Basically, the concept of max for strings is not defined. So believe it.

--
Regards,
Tom Ogilvy

"Tom" wrote in message
...
the max-value of the the following entries is 02/001

01/001
01/002
01/003
02/001

I can solve this with a customized function, but I can't believe that this
can't be solved with a worksheet function.

Tom



"Tom Ogilvy" schrieb im Newsbeitrag
...
What is the max in your sample data - does 02 take precedence over any
01
in
the first two places or is the max the highest of the last 3 digits.

Can a helper column be used next to this column?

--
Regards,
Tom Ogilvy

"Tom" wrote in message
...
Coolio

I suppose that the Code-function fails... I have numbers like this:

01/001
01/002
01/003
02/001

Tom



"Colo" schrieb im Newsbeitrag
...
Hi Tom,

Here is an easy way...please use code function like =CODE(A1), so you
can
use MAX function.


--
Kind Regards
Colo
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Colo of 'The Road of The Cell Masters' :)

URL:http://www.interq.or.jp/sun/puremis/...astersLink.htm


/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/


"Tom" wrote in message
...
Hi

I have a column with alphanumeric data. The max function works only
with
numeric values. How to calculate the max-value of alphanumeric
values?

Tom