0

Basically, I must write a formula on Google Sheets where I can substitute all the occurrences of a certain number for another one after it already appeared once.

For example, a certain string says "Draw {0} cards." Where {0} is a variable number that will be substituted for another string in a later stage (a value, a specific word, whatever).

I must concatenate this result with another specific string that says:

"Deal {0} damage."

So this should look like: "Draw {0} cards. Deal {0} damage." However, the system does not comprise strings like this, so the formula must change the expression to "Draw {0} cards. Deal {1} damage.". It means that whenever a number has already appeared on a string, its next occurrence must be replaced for the following one.

I know that I can use formulas like

=SUBSTITUTE(B27;"0";"1";2)

Where B27 is the cell where I concatenate the two previous strings.

The main problem here is that usually, I will not concatenate just two different strings. Sometimes there are more than 3 or 4 strings put together, and most times those strings have their own variables in a sequence.

For example, if I would concatenate those three different strings, the previous formula would not work properly:

"Draw {0} cards."
"Deal {0} damage to {1} players."
"Gain {0} life and discard {1} cards."

Simpling concatenating those strings would result in:

"Draw {0} cards. Deal {0} damage to {1} players. Gain {0} life and discard {1} cards."

But in order for this to work, I must get a result like:

"Draw {0} cards. Deal {1} damage to {2} players. Gain {3} life and discard {4} cards."

Can someone here can help me with a formula that could solve this problem? Is there any Google Sheets formula that can help? I mean, I'm a game designer, not a programmer, so scripts and some code solutions are a bit harder for me to work with.

Thank you very much for your attention.

player0
  • 124,011
  • 12
  • 67
  • 124
Matheus Funfas
  • 3
  • 1
  • Just to make sure that I understand it correctly: the 'counter' goes up regardless by the initial {value} inside the brackets? So "{0}..{2}..{2}..{3}..{2}..{12}" should turn into "{0}..{1}..{2}..{3}..{4}..{5}"? – bartinmader Jan 22 '19 at 18:06
  • Yes. Exactly. Regardless of the initial values, the final result should always display a sequence of {0}...{1}...{2}... and so on. – Matheus Funfas Jan 22 '19 at 18:45

2 Answers2

0

you can do this like:

="""Draw {"&C2&"} cards."""&CHAR(10)&
 """Deal {"&D2&"} damage to {"&E2&"} players."""&CHAR(10)&
 """Gain {"&F2&"} life and discard {"&G2&"} cards."""

1

or you can do this like:

="""Draw {"&D11&"} cards."""&CHAR(10)&
 """Deal {"&D11+1&"} damage to {"&D11+2&"} players."""&CHAR(10)&
 """Gain {"&D11+3&"} life and discard {"&D11+4&"} cards."""

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks, player0. However, I can't change the existing strings, because they came from a previously established database. The most I can change are the formulas on the cell that concatenates those strings. – Matheus Funfas Jan 22 '19 at 19:11
0

You could regex the strings and replace them.

The following script takes a string as an input and then just replaces all the occurrences of {0}, {2}, {4}, etc. and replaces them with the correct numbers.

Basically, it uses regex to determine how often the pattern occurs and then iterates over the string and makes the changes.

// String used for tests

var string = 'Draw {0} cards.Deal {0} damage to {1} players.Gain {0} life and discard {1} cards.'

// Actual function

function changeString(input){
   
var re = /({[0-9]{1,5}})/g;
var totalMatches = count(input);
  
// Internal function to count the occurrance of matches of {number} (up 
to five digits inside brackets)

function count(input){
  return ((input || '').match(re) || []).length
}

// Internal function to replace the n-th input and return a modified 
string

function replaceNthElement(input, re, n, transform) {
  let count = 0;

  return input.replace(
    re, 
    match => n(++count) ? transform(match) : match);
}

// Iterating over the string in order to change all occurrences of 
{number}

for (var n = 0; n <= totalMatches; n++) {

  input = replaceNthElement(input, re, count => count === n, sstr => '{' + (n-1) + '}');
}
return input;
};

// for demonstration only

console.log("Original String: " + string);
console.log("Changed String: " + changeString('Draw {0} cards.Deal {0} damage to {1} players.Gain {0} life and discard {1} cards.'));

The main concepts come from other articles on Stack Overflow, namely this one and that one.

bartinmader
  • 266
  • 3
  • 10