Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search for data matching some condition | Excel Worksheet Functions | |||
How to search a string from the right ? | Setting up and Configuration of Excel | |||
to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
Extract String based on condition | Excel Discussion (Misc queries) | |||
how to search in excel with condition | Excel Discussion (Misc queries) |