[Solved] Adding formula surrounded by text

[lebyarules]

hi, i was hoping someone can help me create a formula for adding up sums within text.

example:
46 seconds ago TheBod 2,836,824 Gold stolen 65 75 10,589,314 25,747,478,549
48 seconds ago TheBod 2,959,876 Gold stolen 98 113 10,895,895 21,150,941,790
51 seconds ago TheBod 3,424,437 Gold stolen 59 68 10,278,839 22,604,977,150
53 seconds ago TheBod 5,071,999 Gold stolen 39 45 10,969,067 24,029,120,117
56 seconds ago TheBod 4,665,919 Gold stolen 39 45 12,173,374 25,876,793,733
58 seconds ago TheBod 6,401,733 Gold stolen 33 37 10,541,193 24,994,750,953
1 minute ago TheBod 4,839,278 Gold stolen 93 106 9,384,202 27,817,731,085
1 minute ago TheBod 6,817,234 Gold stolen 100 113 9,035,816 23,182,536,814
1 minute ago TheBod 6,537,791 Gold stolen 66 76 10,477,942 21,328,141,954
1 minute ago TheBod 9,918,380 Gold stolen 100 114 8,926,737 24,149,901,946

i need openoffice to add up all the figures automatically that i have put in bold and ignore the rest. is there a way to do this? its not in a table because its pasted from a website and the only constant in the above is "gold stolen"
thanks

[mgroenescheij]

You should copy the data from the website and Paste Special into Calc.
Select as delimiter a space, this will copy the data in different columns.
Format the Numeric cells (for readability) and Sum the column is now a piece of cake.

[keme]

You should copy the data from the website and Paste Special into Calc.
Select as delimiter a space, this will copy the data in different columns.
Format the Numeric cells (for readability) and Sum the column is now a piece of cake.

That will work as expected, but may fail if spaces are allowed within the name ("TheBod" in the example).

The attached file circumvents that, using the defined "constant text" (Gold stolen) as the anchor point.

To make this work without changing my setup, I had to use the SUBSTITUTE() function to remove thousands separators. With my locale (Norwegian), commas are used as the decimal marker. The substitution step is probably not necessary when you have a locale where commas are thousands separators, but it doesn't hurt.

[Charlie Young]

With the data provided in A1:A10

Code: Select all

=SUMPRODUCT(VALUE(MID(A1:A10;FIND("TheBod";A1:A10;1)+7;FIND("Gold stolen";A1:A10;1)-FIND("TheBod";A1:A10;1)-8)))

[lebyarules]

wow. thats what you call great help. thanks so much mgroenescheij, keme, and Charlie Young
much appreciated