Finding maximum number

[jpen]

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

 

[chiello]

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]

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]

Aladin,

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

JH

 

[Aladin Akyurek]

Aladin,

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

JH

What formula exactly did you try?

 

[klb]

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