Finding maximum number

jpen

Active Member
Joined
Jun 19, 2002
Messages
393
Hi all,

I have the next problem. In column A are 5000 rows of data. Between these data is are a number of cells that looks like [testx] where the x is a number from 0 to 999. What I need to know is the maximum x.

Example:

a
s
d
[test0]
f
d
s
[test1]
t
[test2]
w
w

So the result should be x = 2.

Thanks

JH
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
Try this:

Enter in Column B into the first row of data, suppose B4 the following firmula:

=IF(ISNUMBER(SEARCH("test",A4,1)),VALUE(MID(A4,6,LEN(A4)-6)),-1)

Copy down for all rows of data, (suppose B4:B5003) and the enter in cell C1 the formula:

=MAX($B$4:$B$5003)**

** I'm using Excel Italian version: NOT SURE IF FUNCTION MAX IS CALLED MAX IN ENGLISH TOO: USE THE RIGHT NAME

Does it work??

Post for feedback

Ciao :wink:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Assuming that "[" and "]" are part of the test names and the max numbered test is the latest in the sequence:

=SUBSTITUTE(SUBSTITUTE(UPPER(LOOKUP(9.99999999999999E+307,SEARCH("Test",$A$2:$A$13),$A$2:$A$13)),"[TEST",""),"]","")+0
 

jpen

Active Member
Joined
Jun 19, 2002
Messages
393
Aladin,

I tried the formula but get the error #VALUE!. Any ideas?

JH
 

klb

Well-known Member
Joined
Apr 3, 2002
Messages
821
How about in column b:
=IF(LEFT(A1,5)="[test",MID(A1,6,LEN(A1)-6),0)*1

Th *1 forces the result to be a number

Place your max statement in column c
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,040
Members
414,357
Latest member
Gemma_R

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