I have a google sheet which I'm using to record Sales data. I paste my raw data into Sheet A!A1, the raw data contains both text, numbers and 'unusable' characters, which are used in the split formula to define the columns.
I am then using a transpose script to pull this data onto sheet B, pasting the data horizontally +1 last row.
My issue is, when splitting the data, my defining character is ] or [ -
This works well, however, the item recorded in the sale does not split from the quantity - It remains as follows Item xQuantity#. If I change the deliminator to #x this leads to the script splitting text that contains the letter x, as well as doing the desired function of splitting "Item xQuantity" into "Item, Quantity"
Any suggestions would be helpful!
The data needs to be refined into only readable values, and then archived into a secondary sheet
At request, heres some data samples :
This is pasted into SheetA!A1:
Total price $4,864,910. Breakdown per item: [Bag of Chocolate Kisses x200 @ 450 = 90,000] [Box of Tissues x1 @ 300 = 300] [Jacket x66 @ 200 = 13,200]
The above data can be anywhere from 1 line of text, to 100+ lines within a single cell.
I am then using the below formula, to remove unwanted characters, and split into desired columns:
=SPLIT( REGEXREPLACE(Front!A3,"^A-Za-z+]. Breakdown per item:, x#"," "),"[]@=")
Im currently using this to pull the data into the archive sheet, once refined :
function CaptureData() {
var ss = SpreadsheetApp.getActiveSpreadsheet ();
var source = ss.getRange ("Back!8:8");
var destSheet = ss.getSheetByName("Data");
var destRange = destSheet.getRange(destSheet.getLastRow()+1,1);
source.copyTo (destRange, {contentsOnly: true});
}
Pictured below is the Archive Sheet, with desired outputs - Please note, the screenshot currently shows the issue i am experiencing.
As your indicator for the amount of items is a lower case x coming after a blank space and you always start each item naming with a capital X you can nest another REGREPLACE to replace [blank space] x to one of your characters set up to split the string. The following formula performs as you intented to:
=SPLIT( REGEXREPLACE(REGEXREPLACE(A1,"Breakdown per item:"," "), " x", "@"),"[]@=")
I hope this has helped you. Let me know if you need anything else or if you did not understood something. :)
