Google Sheets Formula for combining dice rolls

Question

The Situation: I'm creating a dice notation "Clean Up" formula, so that similar dice rolls are combined. For example: "1D6+1D6" would become "2d6". To complicate things, negative rolls (like "-1D6") can't be summed into the final result (Meaning, "1D6-1D6" does not equal "0"). Why? Because 1D6 does not equal the same number for each roll. So, the first (additive) dice roll could be "6", and then the penalty die roll could be 1, for a total of (6-1)=5.

Added factor of complexity... This isn't just for D6. It's the whole RPG set. D4, D6, D8, D10, D12, D20, and D100.

So, I've gotten SO CLOSE!!! But, the formula... It's LONG... Like, I copied and pasted it into MS Word, and it was two full pages long... You'll see...

https://docs.google.com/spreadsheets/d/14C_n53wgiQL6-rrOl-9IfXPBTMIdnsZxaZ5vmeKXsMU/edit?usp=sharing

(Other than the last minute D10 issue) It works... But, I plan on dragging this formula down the line at least 500 times, so it'll KILL the sheet speed.

The final formula is broken down by dice type. (The final formula is just a TEXTJOIN of all of the individual dice formulas).

While I don't know the solution to shortening this down, I did realize in writing this that perhaps it's not necessary to define each dice. Maybe sorted by the array of unique strings that come after "D" and before the next "+"?!?!

Answer 1

try:

=INDEX(REGEXREPLACE(TEXTJOIN("+", 1, FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(IFNA(TRANSPOSE({
 REGEXEXTRACT(SPLIT(C5, "+"), "^\d+")*1;  REGEXEXTRACT(SPLIT(C5, "+"), "D\d+"); 
 REGEXEXTRACT(SPLIT(C5, "+"), "^-\d+")*1; REGEXEXTRACT(SPLIT(C5, "+"), "D\d+"); 
 REGEXEXTRACT(SPLIT(C5, "+"), "D(\d+)")*1}), 0), 
 "select sum(Col1),Col2,'+',sum(Col3),Col4,Col5 group by Col2,Col4,Col5 order by Col5"), 
 "select Col1,Col2,Col3,Col4,Col5 offset 1", )),,9^9))), " |\+ 0 D\d+", ))