Help with some GS Formulas

Hey guys, @Francisco_Maldonado has a good idea to add to an app I was playing with but I think I need a few more/better formulae to accomplish what we need.

@Robert_Petitto, @ThinhDinh @Darren_Murphy @Jeff_Hager

I am trying to get a formula that helps me get all the items on a list. The list lives in a cell. So far, I can get just the first item on the list.

Here’s a video to explain


@Manan_Mehta is the guy for Google sheets

Something like this?

Screen Shot 2021-01-29 at 10.28.01 PM

In column M:


In column N:


** I’m actually surprised that the first one works - I was expecting to have to anchor it with a newline.

1 Like


That looks like it might work. Is there a way to have them displayed in columns instead of rows?

um, they are in separate columns - just like the example in your video.
Unless I misunderstood?

I mean in one column, say Column M. Sorry for the confusion!

I have them in separate columns because I am trying to isolate the item from the price and amount, so I can have a relation.

I think he wants a split, transpose.

1 Like

ah, okay. So something like this then:


Screen Shot 2021-01-29 at 10.49.41 PM

1 Like

Let me try.

Edit: Can this be used in an Array Formula? @Darren_Murphy

I am trying to use it like this:

1 Like

I don’t think I did it right. @Darren_Murphy :man_facepalming:t6:

Using your Row 3 as an example, the formula would write to row 3 and row 4. Then when row 4 attempts to split and transpose, it’s writing to row 4 and row 5, but row 4 has already been populated by the formula in row 3. Still following? :wink: It’s the overlapping that’s the problem and google isn’t going to start inserting rows to make everything fit. I think you would have to join all the values in the entire column together and then split/transpose, but then nothing would line up correctly (unless that doesn’t matter and there are no other columns associated with each row. What exactly would you like to see as a result? Say you did the above formula on the first 3 items. What would you want the sheet to look like if it had worked?

1 Like

mmm, I was also wondering about that…

1 Like

Hey @Jeff_Hager,

What I am trying to achieve is to get all items separated in individual cells. Look at cell A3, A4, A5, A7, A9. These cells have two item each. On column C, I am using this formula ={"item";arrayformula(IF(A2:A<>"","" & LEFT(A2:A,7),))} but it only gives me one item.

The result is that all items are written in a different cell in a column separately.

The idea behind this is to be able to create an inventory count.

I hope this makes sense.

I would think some sort of query formula on the column would work to combine all of the contents of the the column, then a split/transpose on the results of that query. I’m not entirely sure how the split will handle splitting the last item from row 3 and the first item from row 4, but if it doesn’t work, I suppose you could add a separate delimiter, like a pipe | into the query as a second column, and also add a second split on the pipe, or maybe you could add Char(10) as a column value???


Hey @Jeff_Hager

Would you mind showing me what that formula would look like?

I have been trying it to no avail.


I don’t have time to work on it right now, but maybe tonight or tomorrow I can take a look.

1 Like