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

Mixphonics

New Member
Joined
Sep 21, 2012
Messages
21
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.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

farmerscott

Well-known Member
Joined
Jan 26, 2013
Messages
813
Office Version
  1. 365
Platform
  1. Windows
Mixphonics,

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

FarmerScott
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,046
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 4, 2004
Messages
7,483

ADVERTISEMENT

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


Code:
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Given:

Row\Col
A​
B​
C​
2​
J-100max
3​
J-236
3​
4​
[1]
5​
[2]
6​
J-500
7​
[3]
8​
200​
9​
10​

<tbody>
</tbody>


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).
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

With your data in the range A1:A6, you can run this macro...
Code:
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)...
Code:
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

New Member
Joined
Sep 21, 2012
Messages
21
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Last edited:

Mixphonics

New Member
Joined
Sep 21, 2012
Messages
21
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top