Help with existing array formula

[Greed]

I am using the formula below in a payroll spreadsheet (I was given A LOT of help by an awesome power user/moderator on this site) and I need it to be tweaked a little, but am not sure how to do it.

Currently it is designed to look at an entry like "15 OT" and return the number 15 (J2 being the cell containing the reference to look for (OT)). I would like the formula to allow for finding another code in the reference cell as well.

Ex. If the cell contains "15 OT STAT", return the number 15 when searching for OT, as well as searching for STAT

Currently it will only return the number 15 if I am searching for the first 'chunk' of my payroll code (the OT, but not the STAT)

Please Login or Register  to view this content.

Any help would be greatly appreciated!

[Richard Buttrey]

Hi,

Please upload your workbook so that we can see the request in context. In addition show an example of what you expect the result to be.

Regards

[daddylonglegs]

Assuming only 1 or 2 digit numbers.......always at the start then perhaps try this formula

=SUM(IF(ISNUMBER(FIND(" "&J$2&" "," "&$C5:$I5&" ")),LEFT($C5:$I5,2)+0))

confirmed with CTRL+SHIFT+ENTER

If numbers might be larger change to this

=SUM(IF(ISNUMBER(FIND(" "&J$2&" "," "&$C5:$I5&" ")),LEFT($C5:$I5,FIND(" ",$C5:$I5)-1)+0))

[Greed]

Great - works a trick! Thanks