Want to Use an Array; Not Sure of Syntax

Hi. I want to use an array command that will evaluate a column of text
entries; identify the numeric portion, and finally determine the single
largest number of the array.

Here is a sample/illustration of what I am talking about:

| A | B | C
|
---|--------+----------------------------------------------+----------------+
1 |ID Nos. | Formula to Get Numeric from End of "ID Nos." | Formula
Result |
---|--------+----------------------------------------------+----------------+
2 | t-199 | =VALUE(MID(A7,FIND("-",A7)+1,3)) |
199 |
---|--------+----------------------------------------------+----------------+
3 | t-204 | =VALUE(MID(A7,FIND("-",A7)+1,3)) |
204 |
---|--------+----------------------------------------------+----------------+
4 | t-382 | =VALUE(MID(A7,FIND("-",A7)+1,3)) |
382 |
---|--------+----------------------------------------------+----------------+
5 | | |
|
---|--------+----------------------------------------------+----------------+
6 | | Formula to Get Largest Value C2:C4 | Formula
Result |
---|--------+----------------------------------------------+----------------+
7 | | =LARGE(C2:C4,1) |
382 |
---|--------+----------------------------------------------+----------------+

Thanks for the help.

--Rich K.

ps> if the text representation of excel (above) is misaligned I'll try
again.

Hi!

Here's a couple of possibilities:

Assuming:

All numbers are 3 digits long:

Entered with the key combo of CTRL,SHIFT,ENTER:

=MAX(--RIGHT(A1:A5,3))

OR, if the number of digits may vary but the "T-" is constant in all
entries:

Again, entered with the key combo of CTRL,SHIFT,ENTER:

=MAX(--SUBSTITUTE(A1:A5,"t-",""))

Biff

"RichK" <[email protected]> wrote in message
news:[email protected]...
>
> Hi. I want to use an array command that will evaluate a column of text
> entries; identify the numeric portion, and finally determine the single
> largest number of the array.
>
> Here is a sample/illustration of what I am talking about:
>
> | A | B | C
> |
> ---|--------+----------------------------------------------+----------------+
> 1 |ID Nos. | Formula to Get Numeric from End of "ID Nos." | Formula
> Result |
> ---|--------+----------------------------------------------+----------------+
> 2 | t-199 | =VALUE(MID(A7,FIND("-",A7)+1,3)) |
> 199 |
> ---|--------+----------------------------------------------+----------------+
> 3 | t-204 | =VALUE(MID(A7,FIND("-",A7)+1,3)) |
> 204 |
> ---|--------+----------------------------------------------+----------------+
> 4 | t-382 | =VALUE(MID(A7,FIND("-",A7)+1,3)) |
> 382 |
> ---|--------+----------------------------------------------+----------------+
> 5 | | |
> |
> ---|--------+----------------------------------------------+----------------+
> 6 | | Formula to Get Largest Value C2:C4 | Formula
> Result |
> ---|--------+----------------------------------------------+----------------+
> 7 | | =LARGE(C2:C4,1) |
> 382 |
> ---|--------+----------------------------------------------+----------------+
>
> Thanks for the help.
>
> --Rich K.
>
> ps> if the text representation of excel (above) is misaligned I'll try
> again.
>

Try this (for clarity):


| A | B | C |
---|-------+-------------------------------------+----------------+
1 |ID Nos.| Formula to Get Numeric | Formula Result |
| | from End of "ID Nos." | Formula Result |
---|-------+-------------------------------------+----------------+
2 | t-199 | =VALUE(MID(A7,FIND("-",A7)+1,3)) | 199 |
---|-------+-------------------------------------+----------------+
3 | t-204 | =VALUE(MID(A7,FIND("-",A7)+1,3)) | 204 |
---|-------+-------------------------------------+----------------+
4 | t-382 | =VALUE(MID(A7,FIND("-",A7)+1,3)) | 382 |
---|-------+-------------------------------------+----------------+
5 | | | |
---|-------+-------------------------------------+----------------+
6 | | Formula to Get Largest Value C2:C4 | Formula Result |
---|-------+-------------------------------------+----------------+
7 | | =LARGE(C2:C4,1) | 382 |
---|-------+-------------------------------------+----------------+

Thanks for your answer.

I knew someone would have an elegant solution!

What does the double dashes in front of the SUBSTITUTE function
accomplish?

I get everything else.

Thanks again.

--Rich K.

Hi!

Both RIGHT and SUBSTITUTE are TEXT functions. The values they return are
TEXT.

The -- "converts" the text numbers into numeric numbers.

Biff

"RichK" <[email protected]> wrote in message
news:[email protected]...
> Thanks for your answer.
>
> I knew someone would have an elegant solution!
>
> What does the double dashes in front of the SUBSTITUTE function
> accomplish?
>
> I get everything else.
>
> Thanks again.
>
> --Rich K.
>