I am trying to extract data from the following data-set below that has multiple rows with multiple columns, however each row has not the same amount of columns. I would like to extract the data in the green cells as show in the picture below:
There is a pattern in the data-set. The data I would like to extract is always the third cell from the end of the row.
Since I have no idea how to do this I hope someone has a suggestion for me.
My initial idea was to somehow find a way to merge the last 3 columns into one column. Then copy/paste this data from the merge column in Google Sheets (or with EDT) and then split them again so I would have the data that I want in the first column after splitting. I hope there is actually an easier way, lol.
How would you solve this in EDT? (or in Google Sheets?)
Resources:
The raw data:
43 10 12345-00020 123 Popular Bonsai Tree Design 12345 12.00 120.00
162 10 789-333337 789B Jack Tree Design 789B.123 15.00 150.00
302 10 1515AB-000060 1515AB Black Tree W/Vase - AY 1515AB 81.00 810.00
393 10 954RL Replacements- 10 pcs. - Uniform (= 1 carry box) MOQ 6 boxes 954RL 100.00 600.00
I can’t attach the CSV file with the data-set as a new user, but it is basically the data above, but split in columns.
Looking forward to suggestions to solve this query.
Thank you for helping out!
This is a bit of a tricky one due to the varying number of columns on each row. However you can do it by using Concat Cols to concatenate the data into 1 column (column 19 in this case) and then writing some Javascript to extract the 3rd last value from that column. Make sure the Delimiter you choose for the Concat Cols is not something that can appear in a value.
var a = $(19);
var commaCount = 0;
var newString = "";
for (var i = a.length - 1; i >= 0; i--)
{
c = a[i];
if ( c == ',' )
{
commaCount += 1;
}
else if ( commaCount == 2 )
{
newString = c + newString;
}
}
return newString;
It might also be possible to it with Replace and regular expressions. There may also be a more elegant way using other standard transforms, but I couldn’t think of one.
?