Thanks:  0
Likes:  0

# Thread: find the highest number in a string of numbers

1. ## find the highest number in a string of numbers

is it possible to find the highest number is a string of numbers, in different cells:

cell c3 = 12+13+14
cell d3 = 20+21+22
cell e3 = 12+20+20

there are alot more and was wondering if there was a way for me to just add everything and not worry about the "HIGH Game"? the formula i have =LARGE(C3:K3,1) Which brings the highest number, not the single highest number

2. ## Re: find the highest number in a string of numbers

Jerk24,

Sample raw data:

Excel 2007
CDEFGHIJKL
3396352446857497362

Sheet1

Worksheet Formulas
CellFormula
C3=12+13+14
D3=20+21+22
E3=12+20+20
F3=12+13+14+5
G3=20+21+22+5
H3=12+20+20+5
I3=12+13+14+10
J3=20+21+22+10
K3=12+20+20+10

After the macro:

Excel 2007
CDEFGHIJKL
339635244685749736222

Sheet1

Worksheet Formulas
CellFormula
C3=12+13+14
D3=20+21+22
E3=12+20+20
F3=12+13+14+5
G3=20+21+22+5
H3=12+20+20+5
I3=12+13+14+10
J3=20+21+22+10
K3=12+20+20+10

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
```Option Explicit
Sub GetHighestNumber()
' hiker95, 11/02/2013
' http://www.mrexcel.com/forum/excel-questions/736816-find-highest-number-string-numbers.html
Dim c As Range, rng As Range, h As String, s, i As Long, maxn As Long
Range("L3").ClearContents
maxn = 0
For Each c In Range("C3:K3")
h = c.Formula
s = Split(Right(h, Len(h) - 1), "+")
For i = LBound(s) To UBound(s)
If s(i) > maxn Then maxn = s(i)
Next i
Next c
Range("L3") = maxn
End Sub```
Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetHighestNumber macro.

Let me see if I can change the macro into a user defined function - no macro to run.

3. ## Re: find the highest number in a string of numbers

Thank you. I will see if I can intergrate the Macro. but if you can find a way to do it without one, that would be better... either way Thank you.

I ran the macro, I needed to change Range("L3").ClearContents to read O because that is where I currently have the high game.

s = Split(Right(h, Len(h) - 1), "+") this line is getting a invaild call or arugument error. (i tried with both the oringial post and my adjusted)

any ideas?

4. ## Re: find the highest number in a string of numbers

Jerk24,

In order to continue, and, so that I can get it right this next time:

sensitive data changed
mark the workbook for sharing

5. ## Re: find the highest number in a string of numbers

The site will not work on my computer. If you would like to send me a private message I will email you.

6. ## Re: find the highest number in a string of numbers

Jerk24,

If you would like to send me a private message I will email you.
No, thank you.

One last try:

1. What is the range of cells that contain the formulae?

2. List each cell, and, each cell formula:
C3 = ?
D3 = ?
.....

3. What cell do you want the result in?

7. ## Re: find the highest number in a string of numbers

Ok below is a shot from my sheet. I have wk1 starting in C3. O5 is where i need the High game (the highest individual number out of all cells in yellow)
There are more players, but i can adjust accodingly. So if you basically i will need the data pulled from C3:K3, C5:k5.

the data set is not imporant, since 48 people will not have the same scores. you post early will work for the acutal data.

 Wk 1 Wk 2 Wk 3 Wk 4 Wk 5 Wk 6 Wk 7 Wk 8 Wk 9 43 17 28 36 Wk 10 Wk 11 Wk 12 Wk 13 Wk 14 Wk 15 Wk 16 Wk 17 Wk 18 # Games Points AVG Hi Gm Hi Trp Turks Lst Wk 12 124 10.33 21 43 0 36 10

8. ## Re: find the highest number in a string of numbers

Does the macro output the value you want?

Code:
```Sub MaxAddend()
Dim Pluses As String, Numbers As Variant
With WorksheetFunction
Pluses = .Trim(Join(.Index(Range("C3:K3").Formula, 1, 0), " ")) & _
"+" & .Trim(Join(.Index(Range("C5:K5").Formula, 1, 0), " "))
Pluses = .Trim(Replace(Replace(Pluses, "+", " "), "=", " "))
Range("O5").Value = Evaluate("MAX(" & Replace(Pluses, " ", ",") & ")")
End With
End Sub```

9. ## Re: find the highest number in a string of numbers

Thank you. that works. how would i bring that to the rest of my sheet, and then do it automatically.

10. ## Re: find the highest number in a string of numbers

Originally Posted by Jerk24
Thank you. that works. how would i bring that to the rest of my sheet, and then do it automatically.
What exactly do you mean by "bring that to the rest of my sheet"? You have other groupings like you showed in Message #7? If so, where are they (it is kind of hard to calculate values from, or write output to, other cells if you do not know where they are located at)?