CODEWITHSUNDEEP
arraysif-statementgoogle-sheetsgoogle-sheets-formulaarray-formulas
Simon GIS
Simon GIS

Reputation: 1055

Split In Google Sheets or Excel : based on condition

I have this Google Spreadsheet.

-The product name column is already in a good format. Full word and delimited by "+".

-The second column is meaning there is part of the string or Sugar in the product name.

-The third columns are some product reformated in the good format (full name and "+")

  Product name           Sugar_word_in_product_name       Product reformated 
  Choco + sug,oil                   1                     Choco + Sugar + oil         
  Tablets + Sofa                    0
  Television + table                0
  sugar,oil,ingred                  1                     Sugar + oil + ingredients

I want to return in this format. Ingredients 1, Ingredients 2... 

  Product name           Sugar_word_in_product_name       Product reformated            Ingredients1     Ingredients2     Ingredients3

  Choco + sug,oil                   1                     Choco + Sugar + oil             Choco             Sugar           oil
  Tablets + Sofa                    0                                                     Tablets           Sofa
  Television + table                0                                                     Television        Table
  sugar,oil,ingred                  1                     Sugar + oil + ingredients       Sugar             Oil            Ingredients
                                    0

So basically, I want to split if it's "1" in column "Sugar_word_in_product_name" From "Product reformated"

If its "0" I want to split into columns from "product name".

This work on one condition. =IF(G2=0,SPLIT(F2,"+")).

That's the query I started but not sure how to make it work and returning blanks cells when no values. 

=IF(G2=0,SPLIT(F2,"+",IF(G2=1,SPLIT(H2,"+"))))

Upvotes: 1

Views: 740

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IFERROR(IF(B2:B=1; SPLIT(C2:C; "+"); SPLIT(A2:A; "+"))))

Upvotes: 1

Related Questions