search string with condition
 

I have a range of cells with text and numbers. I need to search the range of
cells for the string with a condition ie, spare-1, spare-2,etc. then
determine what the maximum number is after the "-". assistance is greatly
appreciated.

search string with condition
 

Assuming those entries are in A1:A10, use this array* formula:

=MAX(--RIGHT(A1:A10,LEN(A1:A10)-FIND("-",A1:A10)))

* An array formula has to be committed using Ctrl-Shift-Enter (CSE)
rather than the usual <Enter. If you do this correctly then Excel
will wrap the formula in curly braces { } when viewed in the formula
bar - do not type these yourself. If you subsequently amend/edit the
formula you must use CSE again.

Hope this helps.

Pete


On Jan 18, 1:26*am, pburk wrote:
I have a range of cells with text and numbers. *I need to search the range of
cells for the string with a condition ie, spare-1, spare-2,etc. *then
determine what the maximum number is after the "-". *assistance is greatly
appreciated.

search string with condition
 

Hi,

Try this array formula

=MAX(IF(ISNUMBER(FIND("-",A1:A22)),-MID(A1:A22,FIND("-",A1:A22),10),""))

To enter this as an array press Shift+Ctrl+Enter instead of Enter.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"pburk" wrote:

I have a range of cells with text and numbers. I need to search the range of
cells for the string with a condition ie, spare-1, spare-2,etc. then
determine what the maximum number is after the "-". assistance is greatly
appreciated.