| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Removing Text from Alphanumeric values | Excel Discussion (Misc queries) | |||
| Removing text from Alphanumeric values | Excel Worksheet Functions | |||
| VLOOKUP with numeric and alphanumeric values | Excel Discussion (Misc queries) | |||
| Need to test for alphanumeric value and write numeric values to ce | Excel Worksheet Functions | |||
| how do i enter alphanumeric values? | Excel Discussion (Misc queries) | |||
Linear Mode
