Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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" wrote in message ups.com... 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. |
#3
|
|||
|
|||
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 | ---|-------+-------------------------------------+----------------+ |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
Hi!
Both RIGHT and SUBSTITUTE are TEXT functions. The values they return are TEXT. The -- "converts" the text numbers into numeric numbers. Biff "RichK" wrote in message oups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Where is the bug in my array? | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions | |||
Previous Post - Correct Syntax Query | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |