Finding and converting text in a range

[Rhapsodie]

I have a single column range of variable length. Every cell has Text. I need to find all the cells that contain specific text within the cell text, find another part of the cell text, convert it to a number and add all the numbers together.

I have done this with a function macro but I frequently turn Calculation from Auto to Manual and back in my activities and find the macro often just stops for some reason and I have to select every cell with the function and press enter to make it work again (very painful).

I have included a sample sheet with exactly what I am trying to do. Note that the data in column A can have vaiable lines between the start and end rows, i.e. many entries with or without the target string.

[DonkeyOte]

It might be an idea to post up the UDF you're presently using.

Also, what are the various size options - are you limted to say a few options, ie 56KB, 128KB, 256KB, 512KB etc... ?

[DonkeyOte]

Based on your sample you could use the following CSE rather than a UDF:

F2: =SUM(IF(LEFT($A$2:$A$12,LEN($E2)+1)=$E2&" ",--MID($A$2:$A$12,FIND(":",$A$2:$A$12)+2,3)))
committed with CTRL + SHIFT + ENTER
copy down

But... pending volume of data (ranges) it may still make more sense to use a UDF.

[Rhapsodie]

Perfect! Thanks guys. The sheet isn't big. About 50 sets of site data so that's about 200 cells with the formula. As the sizes never go over 4 chr it's perfect!

Thanks heaps.