# 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.

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

4 Likes

@Manan_Mehta is the guy for Google sheets

Something like this?

In column M:

``````=REGEXEXTRACT(L4,"(.*)")
``````

In column N:

``````=REGEXEXTRACT(L4,"(.*)\$")
``````

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

1 Like

@Darren_Murphy

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:

``````=TRANSPOSE(SPLIT(L4,CHAR(10)))
``````

1 Like

Let me try.

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

I am trying to use it like this:
` ={"Item";ARRAYFORMULA(IF(A2:A<>"","" & TRANSPOSE(SPLIT(A2:A,CHAR(10)))))}`

1 Like

I donâ€™t think I did it right. @Darren_Murphy

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? 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???

2 Likes

Hey @Jeff_Hager

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

I have been trying it to no avail.

Thanks

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

1 Like