Google sheets Array split function formula

Question

I have this Google sheet:

I am using this formula to split my strings in columns

=ARRAYFORMULA(IFERROR(IF(D2:D=1, SPLIT(A2:A, "+"), SPLIT(C2:C, "+"))))


 Molecules                               ID  NAME                           Vitamin_in_name     NAME1            NAME2         NAME3
 VITAMIN B1 + VITAMIN B6 + VITAMIN B12    1  VITAMIN B1,B6,B12                       1         VITAMIN B1       VITAMIN B6    VITAMIN B12
                                          2  LIDOCAINE + VITAMIN B4,B8,B10           1         
                                          3  SALICYLIC ACID + TALC                   0         SALICYLIC ACID   TALC

I would like to return this sheet. IF the word "vitamin_in_name" = 1 and "molecules" row is empty to re-format the data like in ID2 Column name1, name2, name3. how can i do modify my array formula to do that?

 Molecules                               ID  NAME                           Vitamin_in_name     NAME1            NAME2         NAME3         NAME4
 VITAMIN B1 + VITAMIN B6 + VITAMIN B12    1  VITAMIN B1,B6,B12                       1         VITAMIN B1       VITAMIN B6    VITAMIN B12
                                          2  LIDOCAINE + VITAMIN B4,B8,B10           1         LIDOCAINE        VITAMIN B4       VITAMIN B8    VITAMIN B10
                                          3  SALICYLIC ACID + TALC                   0         SALICYLIC ACID   TALC

Could you please share a copy of the spreadsheet you are working on? — Iamblichus
@lamblichious So i would like to return in the second row : LIDOCAINE VITAMIN B4 VITAMIN B8 VITAMIN B10 — Simon GIS

Iamblichus Iamblichus · Accepted Answer · 2020-05-28T12:56:21

Your request is a bit ambiguous, since you didn't specify what should happen if both these conditions are true:

Molecules column is not blank.
Vitamin_in_name is not 1.

I assumed that, in this case, the values should come from column C, and so Vitamin_in_name is not really needed for this.

In any case, I would do the following:

Check if column A is blank with ISBLANK.
If column A is blank then (1) use REGEXREPLACE to replace all , from column C with +VITAMIN, and (2) SPLIT using + as separator.
If column A is not blank, use a simple SPLIT on column A, with + as separator.

The resulting formula could be this:

=ARRAYFORMULA(IFERROR(IF(ISBLANK(A2:A),SPLIT(REGEXREPLACE(C2:C,",","+VITAMIN "),"+"),SPLIT(A2:A,"+"))))

Google sheets Array split function formula

1 Answers