# Help with some GS Formulas **URL:** \ **Category:** Ask for Help\ **Created:** [January 29, 2021, 2:07pm UTC](https://community.glideapps.com/t/help-with-some-gs-formulas/21940 "2021-01-29T14:07:27Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![SantiagoPerez](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/santiagoperez/32/16807_2.png) [@SantiagoPerez](https://community.glideapps.com/u/SantiagoPerez)\ **Post date:** [January 29, 2021, 2:07pm UTC](https://community.glideapps.com/t/help-with-some-gs-formulas/21940/1 "2021-01-29T14:07:27Z")
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 [https://www.loom.com/share/54e1fd521a894e18bf51af13e804f1a6](https://www.loom.com/share/54e1fd521a894e18bf51af13e804f1a6) ---
**Author:** ![PabloMFalero](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pablomfalero/32/31166_2.png) [@PabloMFalero](https://community.glideapps.com/u/PabloMFalero)\ **Post date:** [January 29, 2021, 2:14pm UTC](https://community.glideapps.com/t/help-with-some-gs-formulas/21940/2 "2021-01-29T14:14:20Z")
@Manan_Mehta is the guy for Google sheets ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [January 29, 2021, 2:30pm UTC](https://community.glideapps.com/t/help-with-some-gs-formulas/21940/3 "2021-01-29T14:30:53Z")
Something like this? ![Screen Shot 2021-01-29 at 10.28.01 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/f/7/f7f94ca70ab253b7bb6d73da8d963551add6c6aa.png) In column M: ```auto =REGEXEXTRACT(L4,"(.*)") ``` In column N: ```auto =REGEXEXTRACT(L4,"(.*)$") ``` \*\* I’m actually surprised that the first one works - I was expecting to have to anchor it with a newline. ---
**Author:** ![SantiagoPerez](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/santiagoperez/32/16807_2.png) [@SantiagoPerez](https://community.glideapps.com/u/SantiagoPerez)\ **Post date:** [January 29, 2021, 2:33pm UTC](https://community.glideapps.com/t/help-with-some-gs-formulas/21940/4 "2021-01-29T14:33:10Z")
@Darren_Murphy That looks like it might work. Is there a way to have them displayed in columns instead of rows? ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [January 29, 2021, 2:34pm UTC](https://community.glideapps.com/t/help-with-some-gs-formulas/21940/5 "2021-01-29T14:34:53Z")
um, they are in separate columns - just like the example in your video. Unless I misunderstood? ---
**Author:** ![SantiagoPerez](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/santiagoperez/32/16807_2.png) [@SantiagoPerez](https://community.glideapps.com/u/SantiagoPerez)\ **Post date:** [January 29, 2021, 2:40pm UTC](https://community.glideapps.com/t/help-with-some-gs-formulas/21940/6 "2021-01-29T14:40:43Z")
> [@Darren\_Murphy](#): > > `"(.*)$"` 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. ---
**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\ **Post date:** [January 29, 2021, 2:45pm UTC](https://community.glideapps.com/t/help-with-some-gs-formulas/21940/7 "2021-01-29T14:45:25Z")
I think he wants a split, transpose. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [January 29, 2021, 2:50pm UTC](https://community.glideapps.com/t/help-with-some-gs-formulas/21940/8 "2021-01-29T14:50:08Z")
ah, okay. So something like this then: ```auto =TRANSPOSE(SPLIT(L4,CHAR(10))) ``` ![Screen Shot 2021-01-29 at 10.49.41 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/5/1/51fd0a6b967022144e2d5f5175d3d4fe0a1bb73e.png) ---
**Author:** ![SantiagoPerez](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/santiagoperez/32/16807_2.png) [@SantiagoPerez](https://community.glideapps.com/u/SantiagoPerez)\ **Post date:** [January 29, 2021, 2:51pm UTC](https://community.glideapps.com/t/help-with-some-gs-formulas/21940/9 "2021-01-29T14:51:19Z")
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)))))}` ![Screen Shot 2021-01-29 at 9.04.44 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/5/e/5e04a759180819f354c99f454a4d9d8c39bebaed.png) ---
**Author:** ![SantiagoPerez](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/santiagoperez/32/16807_2.png) [@SantiagoPerez](https://community.glideapps.com/u/SantiagoPerez)\ **Post date:** [January 29, 2021, 3:15pm UTC](https://community.glideapps.com/t/help-with-some-gs-formulas/21940/10 "2021-01-29T15:15:57Z")
I don’t think I did it right. @Darren_Murphy 🤦🏿‍♂️ ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [January 29, 2021, 3:29pm UTC](https://community.glideapps.com/t/help-with-some-gs-formulas/21940/11 "2021-01-29T15:29:11Z")
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? ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [January 29, 2021, 3:36pm UTC](https://community.glideapps.com/t/help-with-some-gs-formulas/21940/12 "2021-01-29T15:36:19Z")
mmm, I was also wondering about that… ---
**Author:** ![SantiagoPerez](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/santiagoperez/32/16807_2.png) [@SantiagoPerez](https://community.glideapps.com/u/SantiagoPerez)\ **Post date:** [January 29, 2021, 3:38pm UTC](https://community.glideapps.com/t/help-with-some-gs-formulas/21940/13 "2021-01-29T15:38:27Z")
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. ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [January 29, 2021, 3:42pm UTC](https://community.glideapps.com/t/help-with-some-gs-formulas/21940/14 "2021-01-29T15:42:53Z")
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??? ---
**Author:** ![SantiagoPerez](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/santiagoperez/32/16807_2.png) [@SantiagoPerez](https://community.glideapps.com/u/SantiagoPerez)\ **Post date:** [January 29, 2021, 3:59pm UTC](https://community.glideapps.com/t/help-with-some-gs-formulas/21940/15 "2021-01-29T15:59:24Z")
Hey @Jeff_Hager Would you mind showing me what that formula would look like? I have been trying it to no avail. Thanks ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [January 29, 2021, 4:04pm UTC](https://community.glideapps.com/t/help-with-some-gs-formulas/21940/16 "2021-01-29T16:04:08Z")
I don’t have time to work on it right now, but maybe tonight or tomorrow I can take a look.