# Split string and apply function on string parts

1. ## Split string and apply function on string parts

Hello,

I have the follwing pattern of strings:

A1: 14+58+58.6+98
A2: 85+98+14+56.3
A3: 11.3+115+ ....

I need to split the string and apply the follwing formula on each part:

=(STRINGPART^2)/(4*PI())

and return the sum of all string parts formulas.

I already did it using VBA:

Function BASALSUM(PAP)
Dim basal As Double
For Each Item In Split(PAP, "+")
basal = basal + ((Item ^ 2) / (4 * WorksheetFunction.Pi))
Next

BASALSUM = basal / 10000

End Function

Can I do the same using excel default formulas?

Thanks  Register To Reply

2. ## Re: Split string and apply function on string parts

With the sample text in A1
This regular formula seems to calculate the correct values: ``Please Login or Register  to view this content.``
EDITED TO INCLUDE THIS ALTERNATIVE:
This seems to work fine, also ``Please Login or Register  to view this content.``
Is that something you can work with?  Register To Reply

3. ## Re: Split string and apply function on string parts

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 ``Please Login or Register  to view this content.``  Register To Reply

4. ## Re: Split string and apply function on string parts

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))  Register To Reply

5. ## Re: Split string and apply function on string parts

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 ``Please Login or Register  to view this content.``  Register To Reply

6. ## Re: Split string and apply function on string parts

Ron, it worked fine to me.

After your explanation, I agree with Ford's statement about us as lesser beings.

Thanks.  Register To Reply

7. ## Re: Split string and apply function on string parts

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)  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1