Separate multiple cells by delimiter into multiple rows

Adrienne Van Halem

I am trying to develop a WFH schedule tool. I have a form that asks managers to check boxes for Week X starting on X Monday Date which days their team will be in the office.

Then I have the formula to convert the check marks into TEAM NAME Date + ","...

On another sheet, I'm trying to convert the text created in COMBINE into multiple rows. I would like it to look like this so I can return a Calendar that shows the dates each team in the office.

I believe I'll need to use MID , FIND and SUBSTITUTE in my formula (not 100% sure how but there's more info out there on that), but I can't figure out how to return 3 rows of COMBINE1 and 2 rows of COMBINE2 relevant to how many dates are present in it, and then how to provide unique results for each return.

Then I'd have to split by a different delimiter probably into TEAM NAME and DATE.

My final product of the calendar view should look like this.

Thank you for your help! I can share sheets if helpful.

Genevieve P.

Hi @Adrienne Van Halem

I have an idea on how to do this, but bear with me...it has a few helper columns, workflows, and formulas.

In your current sheet, create 5 Workflows - one for each Checkbox - to Copy the row over to your second sheet.

These would be very simple, but this would create multiple rows based on the number of checkboxes used in your form. Here's an example of one:

So this means an entry like this:

Turns into two rows in the destination, like this:

(We'll get to the formula in the Date column in a minute.)

Destination Sheet Set-Up

The first thing we'll set up is the Rank for each row to find out how many came in through the automations. These are the 3 columns to the left of the blue columns.

3 Columns:

• Auto-Number System Column (I called Row ID)
• Row Number column using a formula: =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)
• Rank column using a formula based on the Row Number column:

=RANKEQ([Row Number]@row, COLLECT([Row Number]:[Row Number], [Team Name]:[Team Name], [Team Name]@row, [Week Start]:[Week Start], [Week Start]@row), 1)

Now that we can identify what copied row is what... we need to generate the respective date in the blue Date column.

I've used a Nested IF statement to do this, checking the Rank column number and then looking against all the possible checkbox columns for that row to return the correct date. I have to admit I'm certain there's a cleaner way of writing this formula, but this is just how my brain is working tonight:

=IF(Rank@row = 1, IF(M@row = 1, [Week Start]@row, IF(T@row = 1, [Week Start]@row + 1, IF(W@row = 1, [Week Start]@row + 2, IF(Th@row = 1, [Week Start]@row + 3, IF(F@row = 1, [Week Start]@row + 4))))), IF(Rank@row = 2, IF(AND(M@row = 1, T@row = 1), [Week Start]@row + 1, IF(AND(COUNTIF(M@row:T@row, 1) = 1, W@row = 1), [Week Start]@row + 2, IF(AND(COUNTIF(M@row:W@row, 1) = 1, Th@row = 1), [Week Start]@row + 3, IF(AND(COUNTIF(M@row:Th@row, 1) = 1, F@row = 1), [Week Start]@row + 4)))), IF(Rank@row = 3, IF(AND(M@row = 1, T@row = 1, W@row = 1), [Week Start]@row + 2, IF(AND(COUNTIF(M@row:W@row, 1) = 2, Th@row = 1), [Week Start]@row + 3, IF(AND(COUNTIF(M@row:Th@row, 1) = 2, F@row = 1), [Week Start]@row + 4))), IF(Rank@row = 4, IF(COUNTIF(M@row:Th@row, 1) = 4, [Week Start]@row + 3, [Week Start]@row + 4), [Week Start]@row + 4))))

Then you can hide ALL of the columns except the two blue ones in your Calendar sheet!

I whizzed through that, so if you need me to break down how the last Nested IF works, let me know and I'd be happy to clarify further.

Cheers!

Genevieve

Genevieve P.

Hi @Adrienne Van Halem

I have an idea on how to do this, but bear with me...it has a few helper columns, workflows, and formulas.

In your current sheet, create 5 Workflows - one for each Checkbox - to Copy the row over to your second sheet.

These would be very simple, but this would create multiple rows based on the number of checkboxes used in your form. Here's an example of one:

So this means an entry like this:

Turns into two rows in the destination, like this:

(We'll get to the formula in the Date column in a minute.)

Destination Sheet Set-Up

The first thing we'll set up is the Rank for each row to find out how many came in through the automations. These are the 3 columns to the left of the blue columns.

3 Columns:

• Auto-Number System Column (I called Row ID)
• Row Number column using a formula: =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)
• Rank column using a formula based on the Row Number column:

=RANKEQ([Row Number]@row, COLLECT([Row Number]:[Row Number], [Team Name]:[Team Name], [Team Name]@row, [Week Start]:[Week Start], [Week Start]@row), 1)

Now that we can identify what copied row is what... we need to generate the respective date in the blue Date column.

I've used a Nested IF statement to do this, checking the Rank column number and then looking against all the possible checkbox columns for that row to return the correct date. I have to admit I'm certain there's a cleaner way of writing this formula, but this is just how my brain is working tonight:

=IF(Rank@row = 1, IF(M@row = 1, [Week Start]@row, IF(T@row = 1, [Week Start]@row + 1, IF(W@row = 1, [Week Start]@row + 2, IF(Th@row = 1, [Week Start]@row + 3, IF(F@row = 1, [Week Start]@row + 4))))), IF(Rank@row = 2, IF(AND(M@row = 1, T@row = 1), [Week Start]@row + 1, IF(AND(COUNTIF(M@row:T@row, 1) = 1, W@row = 1), [Week Start]@row + 2, IF(AND(COUNTIF(M@row:W@row, 1) = 1, Th@row = 1), [Week Start]@row + 3, IF(AND(COUNTIF(M@row:Th@row, 1) = 1, F@row = 1), [Week Start]@row + 4)))), IF(Rank@row = 3, IF(AND(M@row = 1, T@row = 1, W@row = 1), [Week Start]@row + 2, IF(AND(COUNTIF(M@row:W@row, 1) = 2, Th@row = 1), [Week Start]@row + 3, IF(AND(COUNTIF(M@row:Th@row, 1) = 2, F@row = 1), [Week Start]@row + 4))), IF(Rank@row = 4, IF(COUNTIF(M@row:Th@row, 1) = 4, [Week Start]@row + 3, [Week Start]@row + 4), [Week Start]@row + 4))))

Then you can hide ALL of the columns except the two blue ones in your Calendar sheet!

I whizzed through that, so if you need me to break down how the last Nested IF works, let me know and I'd be happy to clarify further.

Cheers!

Genevieve

Adrienne Van Halem

@Genevieve P. That was brilliant! I understood it perfectly and your help is SO appreciated! I've been banging my head against the wall about this for a week plus, unable to get my mind around a solution! You saved me!

Genevieve P.

@Adrienne Van Halem

Oh good! I'm glad I could help 🙂