0

I'm a complete noob to everything that concerns formulas and scripts on sheets but I thought I'd ask if a certain thing was possible:

I have no idea if this is even feasible in a formula or if I'd be better off by doing it manually but basically I'd like to do as follow:

7 (3.4) - Losjon (1.2), Grafenau (2.2)

Right now I have in a cell (left content before the '-') the total sum of numbers in between brackets in the right cell (so 1+2+2+2=7) and in between brackets in that same left cell the sum of "left" numbers (I don't know how to call them but here the numbers in italic so 1+2=3) and the sum of "right" numbers (in bold italic so 2+2=4). I wondered if this could be automatized using a formula?

2
  • It is unclear whether you have a text string like 7 (3.4) - Losjon (1.2), Grafenau (2.2) and want to extract numbers from it, or if you have numbers and want to build a text string like the one you quote based on those numbers. Please clarify. Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. May 11 at 14:18
  • Sorry about being unclear, I had no idea about how I should word it but yes I want to extract numbers from an existing text string. I made a small spreadsheet based on your advice with an annotated comment explaining what I'd like to do but please if it's not clear enough do not hesitate at all to tell me, I think I am being rather unclear (because I don't know how to explain what I want to do) Here's the link to the sample spreadsheet: docs.google.com/spreadsheets/d/… May 11 at 17:39
0

I have added a new sheet ("Erik Help") to your sample spreadsheet. There you will find the following array formula in D1 (highlighted in green):

=ArrayFormula({"Amount - Total (Male.Female)";IF(A2:A="",,MMULT(VALUE(REGEXEXTRACT(ARRAY_CONSTRAIN(SPLIT(REGEXREPLACE(E2:E,"[^0-9\.\(]","")&REPT("(0.0",10),"("),COUNTA(A2:A),10)&"","\d+")),SEQUENCE(10,1,1,0))+MMULT(VALUE(REGEXEXTRACT(ARRAY_CONSTRAIN(SPLIT(REGEXREPLACE(REGEXREPLACE(E2:E,"\,|\)","~"),"[^0-9\~\.\(]","")&REPT("(0.0",10),"("),COUNTA(A2:A),10)&"~","(\d+)~")),SEQUENCE(10,1,1,0))&" ("&MMULT(VALUE(REGEXEXTRACT(ARRAY_CONSTRAIN(SPLIT(REGEXREPLACE(E2:E,"[^0-9\.\(]","")&REPT("(0.0",10),"("),COUNTA(A2:A),10)&"","\d+")),SEQUENCE(10,1,1,0))&"."&MMULT(VALUE(REGEXEXTRACT(ARRAY_CONSTRAIN(SPLIT(REGEXREPLACE(REGEXREPLACE(E2:E,"\,|\)","~"),"[^0-9\~\.\(]","")&REPT("(0.0",10),"("),COUNTA(A2:A),10)&"~","(\d+)~")),SEQUENCE(10,1,1,0))&")")})

This formula will produce the header and all results for Column D.

Since the formula is quite complex and designed for a unique/custom situation, it is being provided as-is without explanation.

0

Update: take unspecified gender cases into account per the asker's new requirements.

This may be easiest to do with a fill-down formula using regular expressions and split(). Try this:

=arrayformula( 
  sum( iferror( split( regexreplace(E2, "[^\d]+"," "), " ") ) ) 
  & " (" & 
  sum( iferror( regexextract( trim( split( regexreplace(" " & E2 & " ", "[^\d\.]+", "π µ"), " ", false, true ) ), "(?:^µ)(\d+)" ) ) + 0 ) 
  & "." & 
  sum( iferror( regexextract( trim( split( regexreplace(" " & E2 & " ", "[^\d\.]+", "π µ"), " ", false, true ) ), "(?:^µ\d+\.)(\d+)" ) ) + 0 ) 
  & 
  if( 
    regexmatch(E2, "\d+\.\d+\.\d+"), 
    "." & 
    sum( iferror( regexextract( split( regexreplace(" " & E2 & " ", "[^\d\.]+", "π µ"), " ", false, true ), "(\d+)π" ) ) + 0 ), 
    "" 
  ) 
  & ")" 
)

See the new Solution sheet in your sample spreadsheet. The formulas are in cells D2:D9.

To learn the exact regular expression syntax used by Google Sheets, see RE2.

3
  • Thank you so much both of you! This is a tremendous gain of time for me and it helps a lot, thanks a lot. Just one last question, I added another situation in the sheets (highlighted in green) where I have unspecified gender specimens (instead of males and females) and in that case of course the formula you gave, @doubleunary, doesn't work (because of course this wasn't a type ex. I gave before). Would there be any way to edit the current formula to take that into consideration if possible? May 11 at 20:32
  • Thank you so much once again! And indeed, this is one of my first times on this site so I'll take a look at the help page. One last thing, the code works greatly except for one tiny thing: when there is a zero (so let's say for ex. 1.0), it counts it as whatever other number there is in the case (so here, 1, which thus becomes 1.1 in the total sum case). I've put two exemples of this in the sample spreadsheet (highlighted in pink) if you have any desire or time to look at it. May 11 at 21:13
  • Edited the answer per your new requirements. You have accepted Erik's post as the best answer. If that is really the case, please adjust his formula to your requirements, and ask him for more help if you have trouble with his solution. May 12 at 5:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.