Google Sheets – Editing Comma Delimited Cell to Replace Some Items

google sheets

I have a cell that contains mixed input of one or more delimited items eg:

apple,ABC1,banana,BBC2,FGC3

I need to replace the items that contain ALPHA code eg ABC1 (from a finite list of about 25 codes)

with a new sequence (eg ABC1 becomes avocado,apricot,carrot, eg BBC2 becomes plum,damson,leek)

so that the new output looks like this

apple,avocado,apricot,carrot,banana,plum,damson,leek... etc

Can I do this with a formula?

I've tried switch and if but these only change the entire cell when a single item gets matched…

=SWITCH(Q2, 
"ABC1", "avocado,apricot,carrot",
"BBC2", "plum,damson,leek"
)

EDIT – thanks for feedback already, more info will be helpful here… This cell is in one column of about 400 rows, and I'm looking at creating a formula that can iterate through all rows. Also the number of delimited items in each cell varies (eg the next row might look like this: ABC1,banana)

Best Answer

Please use the following

=ARRAYFORMULA(REGEXREPLACE(
                 REGEXREPLACE(N1:N3,"(ABC1)","xxx,XXX,xx"), 
                                    "(BBC2)","z,zz,xyz"))

(you should to adjust ranges and values to your needs)

Functions used:

Code

function result(range) {
  var splitCol = 1; // split on column B
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][splitCol].split("\n");    
    for(var j=0, jLen=s.length; j<jLen; j++) {
      var output1 = []; 
      for(var k=0, kLen=range[0].length; k<kLen; k++) {
        if(k == splitCol) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

Explanation

The script evaluates each row, and particularly the second column of each row (in JavaScript arrays are zero based, so column 2 corresponds to index 1 of the array). It splits the contents of that cell, into multiple values and uses the "\n" as delimiter (line feed). After that it adds the existing info to an array and only add the individual results, when it hits index 1 (k == 1). The newly prepared row is then added to another array, that's being returned to show the result.

Screenshot

data
enter image description here

result
enter image description here

Example

I've created an example file for you: multi-line cells into new rows.

Add the script under Tools > Script editor.
Save the script, the name doesn't matter.
Use the custom function in a cell just like a built in (see the screenshot above).

Best Answer

Related Solutions

Google-sheets – In a Google spreadsheet, how to list unique items, modified by a regexp

Google Sheets – How to Split Multi-Line Cells into New Rows

Code

Explanation

Screenshot

Example

Related Topic