Split string and apply function on string parts

**fandreacci** · 09-24-2015, 04:11 PM

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

**Ron Coderre** · 09-24-2015, 04:20 PM

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?

**Ron Coderre** · 09-24-2015, 04:27 PM

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.

**FDibbins** · 09-24-2015, 05:44 PM

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))

**Ron Coderre** · 09-25-2015, 10:24 AM

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.

**fandreacci** · 09-25-2015, 12:30 PM

Ron, it worked fine to me.

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

Thanks.

**Ron Coderre** · 09-25-2015, 12:34 PM

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)

Split string and apply function on string parts

LinkBack

Thread Tools

Rate This Thread

Display

Split string and apply function on string parts

Re: Split string and apply function on string parts

Re: Split string and apply function on string parts

Re: Split string and apply function on string parts

Re: Split string and apply function on string parts

Re: Split string and apply function on string parts

Re: Split string and apply function on string parts

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Split String at every "/" then put split string results into a multiline textbox.

Advanced Split Function: Need to pull text string in quotes, within a text string

[SOLVED] Split a string into parts

[SOLVED] Searching a string to see if two parts of the string are equal

Split String into 4 Parts - Max 30 Characters per Cell and Whole Words

[SOLVED] Dim String, and divide/split into parts

Can excels Split() function split a string up at multiple spots?

Bookmarks

Bookmarks

Posting Permissions