Google Sheets - extract text between two characters

Question

I have a form connected to Google Sheets which is populating a dynamic matrix into a single column per row for a customer claim. E.g. one customer claim by PO number for a number of products (containing SKU name, article number, UoM, quantity)

enter image description here

I want to split out each item by customer, PO number and reason for claim (see desired outcome tab in spreadsheet) like this enter image description here

https://docs.google.com/spreadsheets/d/1NeVdCVA0oayx1D3UC8WBxv4SJtrZiTyOf2v3zpnN2Sc/edit#gid=219062088

The formula I am trying to use split the products removing the text before is =(IFERROR(ArrayFormula(((REGEXEXTRACT(F2,": (.+)"))SPLIT(E2:E, ",")), " ")))

I still haven't worked out how transpose into a new table format. Any help much appreciated :)

the spreadsheet you shared can't be accessed. Pease fix permissions. — JPV
try this formula =ArrayFormula(query(split(transpose(split(Join(char(10);E2:E);char (10)));",:");"select Col2,Col4,Col6,Col8")) — Sergey
Hi @JPV sorry had restrictions limited please see spreadsheet below docs.google.com/spreadsheets/d/… — Beth L
Cheers @Sergey ! That worked well. The only other thing I need is for each row to be associated with the customer name and PO number and reason for claim i.e. Columns A B and C - do you know how I could achieve that? Appreciate your help :) — Beth L

JPV JPV · Accepted Answer · 2021-05-13T06:12:59

In the sheet 'JPV Help' I entered

=ArrayFormula(query(split(regexreplace(flatten(iferror('Product Breakdown'!A2:A&"/ "&substitute('Product Breakdown'!B2:B, "po",)&"/ "&'Product Breakdown'!C2:C&"/ "&split('Product Breakdown'!D2:D, char(10)))), "(,)(\s[A-Z]\w+\:)", "/$2"),"/:"), "Select Col1, Col2, Col3, Col5, Col6, Col8, Col10 where Col5 <>''"))

See if that works for you?

Google Sheets - extract text between two characters

1 Answers