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?
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.
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.
ah, okay. So something like this then:
=TRANSPOSE(SPLIT(L4,CHAR(10)))
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)))))}
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?
mmm, I was also wondering about that…
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.
Thanks
I don’t have time to work on it right now, but maybe tonight or tomorrow I can take a look.
