Search for the highest number in a column between two brackets [ ]

[Mixphonics]

Hello fellow Excel users,

I am hoping that someone might know of a way to search for the highest number in a column between two brackets?

I have a column that has different types of text.

Example:
J-100
J-236
[1]
[2]
J-500
[3]

is there a way to search for the highest value of between the bracketed numbers?

I've searched around on the forum and couldn't find the answer, any help would be appreciated.

Thank you.

 

[farmerscott]

Mixphonics,

Can you use a helper column? Then use a combination of functions, LEFT, RIGHT and FIND to remove the brackets.

FarmerScott

 

[Rick Rothstein]

Give this array-entered** formula a try...

=MAX(IFERROR(0+SUBSTITUTE(SUBSTITUTE(A1:A6,"[",""),"]",""),))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[StephenCrump]

Or, if there is any possibility that the non-[..] values could be interpreted as numbers, e.g. a date (it's hard to know from such a small data sample), perhaps:

{=MAX(IFERROR((LEFT(A1:A6)="[")*MID(A1:A6,2,LEN(A1:A6)-2),0))}

 

[jim may]

With your data in the range A1:A6, you can run this macro...

Sub Foo()
Dim arr(), i As Integer
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A1:A" & LR)
i = 1
For Each C In Rng
    If Left(C, 1) = "[" Then
        t = WorksheetFunction.Substitute(C, "[", "")
        t = WorksheetFunction.Substitute(t, "]", "") + 0
        ReDim Preserve arr(i)
        arr(i) = t
        i = i + 1
        t = ""
    End If
Next C
MsgBox "The Max number in your selection is " & WorksheetFunction.Max(arr())
End Sub

 

[Aladin Akyurek]

Given:

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]J-100[/TD]
[TD][/TD]
[TD]max[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]J-236[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][1][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][2][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]J-500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][3][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

200​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


C3, control+shift+enter, not just enter:

=MAX(IF(ISNUMBER(SEARCH("
[*]",A2:A10)),SUBSTITUTE(SUBSTITUTE(A2:A10,"[",""),"]","")+0))

This excludes numbers (numbers which are not enclosed in brackets).

 

[Rick Rothstein]

With your data in the range A1:A6, you can run this macro...

Sub Foo()
Dim arr(), i As Integer
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A1:A" & LR)
i = 1
For Each C In Rng
    If Left(C, 1) = "[" Then
        t = WorksheetFunction.Substitute(C, "[", "")
        t = WorksheetFunction.Substitute(t, "]", "") + 0
        ReDim Preserve arr(i)
        arr(i) = t
        i = i + 1
        t = ""
    End If
Next C
MsgBox "The Max number in your selection is " & WorksheetFunction.Max(arr())
End Sub

Using Aladin's formula as a basis, you can write the macro this way without using a loop (actually, although lengthy, it's a one-liner)...

Sub FooToo()
  MsgBox "The Max number in your selection is " & Evaluate(Replace( _
         "MAX(IF(ISNUMBER(SEARCH(""[*]"",A2:A10)),SUBSTITUTE(" & _
         "SUBSTITUTE(A2:A10,""["",""""),""]"","""")+0))", "@", _
         "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row))
End Sub

 

[Mixphonics]

Thank you for all of your replies.

I tried the macro and it does work but a popup box is not ideal for what I am trying to do.
A helper column is not ideal either.

I was hoping for a formula that would give me the max value and the formulas above didn't seem to work, I have over 300 rows of data and will be adding to it.

Something like the macro would be ideal if I could use it as a formula.

 

[Rick Rothstein]

I was hoping for a formula that would give me the max value and the formulas above didn't seem to work, I have over 300 rows of data and will be adding to it.

I am not sure why Aladin's formula in Message #6 did not work for you (once adjusted for the range, of course)... did you remember to commit his formula using CTRL+SHIFT+ENTER instead of just Enter by itself? You need to do it that way because it is an "array formula" and committing the formula that way tells Excel how to handle the calculations for each cell in the range.

 

[Mixphonics]

Thanks Rick, I was pressing CTRL+SHIFT+ENTER when the cell was selected not in the formula box and that was my problem.

It works now.

Thank you Aladin for the formula!