Split string and apply function on string parts

With the sample text in A1
This regular formula seems to calculate the correct values:

=IFERROR(SUMPRODUCT((INDEX(--TRIM(MID(SUBSTITUTE("+"&A1,"+",REPT(" ",LEN(A1))),LEN(A1)
*ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,"+",""))+1)),LEN(A1))),0)^2)/(4*PI())),0)

EDITED TO INCLUDE THIS ALTERNATIVE:
This seems to work fine, also

=IFERROR(SUMPRODUCT(((0&TRIM(MID(SUBSTITUTE(A1,"+",REPT(" ",100)),(ROW(INDIRECT("1:"&(LEN(A1)
-LEN(SUBSTITUTE(A1,"+",""))+1)))-1)*100+1,100)))^2)/(4*PI())),0)

Is that something you can work with?

Here's a much shorter alternative...if you know the maximum values that might occur
In this formula, I'm assuming no more than 5 values

=SUM(((0&TRIM(MID(SUBSTITUTE(A1,"+",REPT(" ",100)),{1,101,201,301,401,501},100)))^2)/(4*PI()))

Ron, for us lessor beings, care to explain how that SP works?
I get the actual SP and the ^2 etc part, I guess its the part that creates teh array...
LEN(A1)*ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,"+",""))+1))

Sure Ford...Here's the incremental breakdown
Here's the base formula:
=IFERROR(SUMPRODUCT(((0&TRIM(MID(SUBSTITUTE(A1,"+",REPT(" ",30)),(ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"+",""))+1)))-1)*30+1,30)))^2)/(4*PI())),0)

I'll start with removing the IFERROR (since you know what that does) and incrementally resolve sections of the formula

• Plus signs replaced by 30 spaces (I used 30 this time so the formula would fit better on the forum)
• PI x 4 resolved
Note: (0 is pre-pended as a leftover from using a static number of sections, say 5, in case there are less than 5). It's not necessary in this version
=SUMPRODUCT(((0&TRIM(MID("14                              58                              58.6                              98",(ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"+",""))+1)))-1)*30+1,30)))^2)/(12.5663706143592))

• Determining number of sections by subtracting the length without plus signs from the length with plus signs
=SUMPRODUCT(((0&TRIM(MID("14                              58                              58.6                              98",(ROW(INDIRECT("1:"&(13-LEN("145858.698")+1)))-1)*30+1,30)))^2)/(12.5663706143592))

• Partial resolutions of section lengths calculations 
=SUMPRODUCT(((0&TRIM(MID("14                              58                              58.6                              98",(ROW(INDIRECT("1:"&(13-10+1)))-1)*30+1,30)))^2)/(12.5663706143592))

• Partial resolutions of section lengths calculations
=SUMPRODUCT(((0&TRIM(MID("14                              58                              58.6                              98",(ROW(INDIRECT("1:4"))-1)*30+1,30)))^2)/(12.5663706143592))

• Partial resolutions of section lengths calculations
=SUMPRODUCT(((0&TRIM(MID("14                              58                              58.6                              98",{0;1;2;3}*30+1,30)))^2)/(12.5663706143592))

• Location of each section length to be used by the MID function
=SUMPRODUCT(((0&TRIM(MID("14                              58                              58.6                              98",{1;31;61;91},30)))^2)/(12.5663706143592))

• Parsing the new text into 30-character sections
=SUMPRODUCT(((0&TRIM({"14                            ";"  58                          ";"    58.6                      ";"        98"}))^2)/(12.5663706143592))

• Trimming spaces off each section  
=SUMPRODUCT(((0&{"14";"58";"58.6";"98"})^2)/12.5663706143592)

• Converting each section to numeric
=SUMPRODUCT({196;3364;3433.96;9604}/12.5663706143592)

• Performing the division on each section
=SUMPRODUCT({15.5971844230057;267.698614280567;273.265854189922;764.26203672728})

• End result
=1320.82368962077

Glad you got something you can use!


If that resolves your issue, please take a moment to mark this thread as SOLVED (from the Thread Tools menu)