Adding numbers within a sentance

[hutchyp]

I'm trying to create a formula to add numbers within a sentence. For example, Passed 4 of 4 Courses Passed 4 of 5 Courses. I want to add all the 4's.

 

[C Moore]

You could use a UDF. This udf will handle as many "Passed" in a cell exists, but fails if y in "Passed x of y courses" is triple digits. In a module input the following

Function PassedCourses(target As Range)
    startnum = 1
    lookhere = 1
Do
    lookhere = InStr(startnum, target, "Passed")
    startnum = lookhere + 1
        If lookhere = 0 Then GoTo answer
    coursespassed = Mid(target, Len("Passed") + startnum, 1) * 1
    totalpassed = coursespassed + totalpassed
    i = i + 1
Loop
answer:
    PassedCourses = totalpassed
End Function

 

[hutchyp]

Thanks. I ended up using the following:

=(IFERROR(MID(C7,8,2)*1,0))