Google Sheets Combining Like Dice Rolls (While removing Integers)-CodePudding

My apologies for asking an incomplete question previously.

This is what I'm trying to accomplish.

I'm building a TTRPG sheet that automatically combines dice rolls, bonuses (additive) and penalties (subtractive) from a variety of sources. All of this data is expressed as either dice notation (D4, D6, D8, D10, D12, D20, and D100) or an Integer (1, 2, 4, 6), or both (combined). These also include negative values (-1D4, -1D6, -2, etc.). The goal isn't to generate the random numbers, but instead combine like dice together for the player to roll manually (I tried the automatic random numbers... Players were not happy about it.)

So, the goal is to combine likes, so something like: "1D6 1D6" would become "2D6". However, because penalties could outweigh the bonus, you can't combine "1D6 1D6 -1D6" into "1D6". (Since each of the rolls could be a different number, such as "6 6-1" compared to "1 1-6").

Additionally, Integers (2, 4, 6, 8, etc.) are by necessity handled in a different part of the sheet, so the goal is to strip the integers out from the output. (The reason for stripping them out has nothing to do with formula complexity, but other game factors that require it to be viewed separately.)

Here are some examples of typical inputs and expected outputs:

1D6 1D4 1D8 -1D4 1D6 2 = 1D4 -1D4 2D6 1D8 (Notice the integer is removed)

1D6 2 0 1 8 = 1D6 (Because all integers have been stripped out)

1D20 -1D4 2D6 0 1D6 -1D6 = -1D4 3D6 -1D6 1D20

(Yes, negative numbers will have the " -" in front of them).

My original "mostly working" formula was 2 solid pages long when copied/pasted into MS Word. This formula will be repeated THOUSANDS of times, so smaller/faster makes a huge difference in the overall scheme of things. Two previous amazing Spreadsheet Wizards (Player0 and TheMaster) gave great answers, but I failed to disclose the integer as a part of the overall process.

The table below shows the formula that works for the first example, but not the second (gives "2D" in the output).

CodePudding user response：

For original explanation, see