# Largest Value Within a Cell

#### rompetelo

##### New Member
Hi All,

A bit of a complicated one, I have a list with a number of values within a cell. I was wondering If there was a way to extract the largest value. See example below with the solution on the third column.

 Bar Coffee Cat Dog Car Drive Blue Pink Orange Apple Green Tomato 1312 500 600 455 1 13 312671 1315 13126710 671 55 20 13126710 Orange Apple Green Tomato 13126710 671 55 20 13126710 Bar Coffee Cat Dog 1312 500 600 455 1312

<tbody>
</tbody>

many thanks,

Rompetelo

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### rompetelo

##### New Member
I've found this VBA code that sums up all the values within a cell - anyway to modify it to make it return the largest value?
Code:
``````Function SumNums(pWorkRng As Range, Optional xDelim As String = " ") As Double    Dim arr As Variant
Dim xIndex As Long
arr = Split(pWorkRng, xDelim)
For xIndex = LBound(arr) To UBound(arr) Step 1
SumNums = SumNums + VBA.Val(arr(xIndex))
Next
End Function``````

#### gallen

##### Well-known Member
This should work assuming they are separated with a line feed:

Code:
``````Sub GetLargest()
Dim s As String
Dim sSplit() As String
Dim lMax As Long, l As Long

s = Range("A1").Value 'Change as needed or set to active cell

'Assuming the numbers are spearated by a line feed(Alt & Return in a cell):
sSplit = Split(s, vbLf)

For l = 0 To UBound(sSplit)
If IsNumeric(sSplit(l)) Then
If sSplit(l) > lMax Then lMax = sSplit(l)
End If
Next l

MsgBox "Largest value is " & lMax

End Sub``````

#### István Hirsch

##### Well-known Member
Give this formula a try (enter with Ctrl + Shift + Enter):

=MAX(IFERROR(--MID(SUBSTITUTE(CHAR(10)&B1,CHAR(10),REPT(" ",200)),200*ROW(\$1:\$100),200),0))

Last edited:

#### rompetelo

##### New Member
Awesome! tried both and they work amazingly. For convenience Ill use Istvan as its a formula but thank you both!

You guys are geniuses!

Replies
7
Views
2K
Replies
4
Views
908
Replies
3
Views
569
Replies
4
Views
660
Replies
4
Views
1K

1,191,682
Messages
5,987,987
Members
440,124
Latest member
dippy_egg

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back