+ Reply to Thread
Results 1 to 7 of 7

Split string and apply function on string parts

  1. #1
    Registered User
    Join Date
    03-26-2007
    Posts
    15

    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

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    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?
    Last edited by Ron Coderre; 09-24-2015 at 04:32 PM.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    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.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    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))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    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.
    Last edited by Ron Coderre; 09-25-2015 at 10:26 AM.

  6. #6
    Registered User
    Join Date
    03-26-2007
    Posts
    15

    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.

  7. #7
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Split String at every "/" then put split string results into a multiline textbox.
    By Andrew Andromeda in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2015, 01:49 AM
  2. Advanced Split Function: Need to pull text string in quotes, within a text string
    By Zamboni in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-27-2013, 04:38 PM
  3. [SOLVED] Split a string into parts
    By LeahS in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-01-2013, 09:33 AM
  4. [SOLVED] Searching a string to see if two parts of the string are equal
    By cheal2 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-18-2013, 09:58 AM
  5. Split String into 4 Parts - Max 30 Characters per Cell and Whole Words
    By keithm_007 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-22-2012, 05:56 AM
  6. [SOLVED] Dim String, and divide/split into parts
    By Hallet in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-19-2012, 12:37 PM
  7. Can excels Split() function split a string up at multiple spots?
    By 111StepsAhead in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-14-2011, 02:36 PM

Bookmarks

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