find value in a cell where cell contains text and value

[cdymott]

I would like to be able to separate data in a cell and pick the value where the text within the cell is matched.

eg a cell contains "rov6.25afr4". whereby the rov and afr depict departments, and 6.25 and 4 are the hours worked in the respective departments.

I want to be able to sum all the values in cells that have rov and afr which will give the total hours worked in each department.

eg 3 cells contain:
"rov6.25afr4"
"afr4"
"rov5"

I want a result that gives rov 11.25 and afr 8

Can anyone help?!!

[tim201110]

i changed "." to ","
=IFERROR(-LOOKUP(1,-MID(MID($A3,FIND(B$2,$A3)+LEN(B$2),5)&" ",MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID($A3,FIND(B$2,$A3)+LEN(B$2),5)&1/17)),ROW($A$1:$A$5))),"")

[Glenn Kennedy]

or...

=IFERROR(-LOOKUP(1,-LEFT(TRIM(MID($A3,FIND(B$2,$A3)+3,255)),{1,2,3,4,5,6,7,8,9})),"")

[kvsrinivasamurthy]

A single UDF will give end result .Is it ok.

[cdymott]

Thanks Glenn - spot on!!

[cdymott]

Hi Tim,

Thanks.
I need the "." to remain as is though.
Glenn's solution is the one that worked for me.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.