Help needed with text string adding for MID and Right whole/fractional Values

[JadeEyes]

Help needed with text string having adding for MID and Right Values
Example:
A2 = Active Resource Demand, “PM = 4.5, CE = 3”
A3 = Planned Resource Demand, “PM = 1.5, CE = 2.5”
A4 = Total Resource Demand, “PM = 6, CE = 5.5”

Formulas for cells A1:A3 work perfect so I removed those formulas and replaced with text strings.
Issue exists with cell A4. A4 formula does not work for adding fractional resources from cells A2:A3 (e.g., cell A3). So, There should be a simple solution other than using a bunch of IF statements...rigth?

Current formula for whole numbers is:

Please Login or Register  to view this content.

New formula for whole/fractions is?

[Leizure]

The problem is the length of the string you are getting the value from (1), for the fraction it needs to be (3).

Assuming the values could be whole or fraction you need to find the end point of the first number and then calculate the start point of the second.

[AndyPS]

I played around with using a bunch of MID(A2,Search("CE =",A2......... etc to locate the numbers. It got very messy and I would probably go for a user defined function to do the job. In that e.g. a SPLIT function could break the string into parts that are extracted, evaluated and summed. With a bit of extra code it could be arranged to accept a range and sum the whole lot.
Unfortunately I haven't got time to write something right now but it is an interesting challenge.

[JadeEyes]

Leizre, I forgot to state the text string length varies so I cannot dictate the position of of digits beyond the first 6. Thanks for your suggestion.

AndyPS, after looking as a few examples of search, this is exactly what i'm looking for however I lack the skills to get past the current bump.

Can someone help provide the solution using the search function?

[Leizure]

OK, If PM = and CE = are constant this formula should work:

="PM = "&VALUE(MID(A2,FIND("=",A2)+2,(FIND("C",A2)-1)-(FIND("=",A2)+2)))+VALUE(MID(A3,FIND("=",A2)+2,(FIND("C",A3)-1)-(FIND("=",A3)+2)))&" CE = "&VALUE(RIGHT(A2,LEN(A2)-FIND("E",A2)-2))+VALUE(RIGHT(A3,LEN(A3)-FIND("E",A3)-2))

Dave.

[JadeEyes]

Leizure Dave, You're the man! Thank you!!! This works perfectly for me.