0

I have 1 column of data that I want to split to units & product name.

enter image description here

enter image description here

In the desired format its not really necessary for headers, so if we need to omit them that's fine. I normally have around 200+ rows of data that I want to split. Currently I do it by, pasting column to excel & splitting via fixed column width. It does the work as I desire and also without any spaces before the new columns. But I want to be able to do this in sheets itself if possible.

I found this great apps script in stackoverflow that works great for my needs. But only problem is that it somehow seems to put extra spaces on some of the data rows. So is it possible to set the 2nd substring to start from the first letter instead of counted characters. split text to column script

  for (var i=startrow-1;i<LR;i++){
var outputrow=[];
var unit = values[i][0].substring(0,1);
var name = values[i][0].substring(**SET TO FIRST TEXT CHARACTER**);

My sample sheet: https://docs.google.com/spreadsheets/d/1uO6mw6T9vK9mN8ZRtCJXX5UT8yV_rL-4_fD2TZbiDbk/edit?usp=sharing

Genny
  • 133
  • 1
  • 6

1 Answers1

3

In D2 I entered

=ArrayFormula(if(len(DATA!A2:A), trim(regexextract(DATA!A2:A, "^(\d+)\s(.*)$")),))

If you want to have the first column formatted as number you can try

=ArrayFormula(if(len(DATA!A2:A), {regexextract(DATA!A2:A, "^(\d+)\s")+0, trim(regexextract(DATA!A2:A, "\s(.*)$"))},))

Or with script try something like

function myFunction() {
  const ss = SpreadsheetApp.getActive()
  const values = ss.getRange('DATA!A2:A').getValues().filter(String).flat().map( c => {
    const splitted = c.split(" ");
    const num = splitted.shift();
    return [Number(num), splitted.join(" ").trim()]
  })

  ss.getSheetByName('Result').getRange(2, 1, values.length, 2).setValues(values);
}
JPV
  • 26,499
  • 4
  • 33
  • 48
  • Thank you for your response JPV. But the script doesn't seem to work. I was hoping to insert that part into the script too instead of formula – Genny Jun 07 '22 at 16:02
  • If you run the script and check the console you should see the correct result. All what is left to do for you is complete the code so that it actually writes the output the sheet. – JPV Jun 07 '22 at 17:58
  • Also, if you say it doesn't work, please specify what is not working. – JPV Jun 07 '22 at 17:58
  • 1
    In the sheet you shared I added ss.getSheetByName('Result').getRange(2, 1, values.length, 2).setValues(values); If you run the function now it will write to the sheet 'Result'. Make sure the clear the sheet first before running. Then check if it works. – JPV Jun 07 '22 at 18:04
  • It works! Thank you for the help JPV. I'm sorry about my unclear explanations, its just that I'm a novice. I'm learning now though. :) – Genny Jun 08 '22 at 02:23
  • No worries @Genny ! Glad to hear everything works now. Cheers, JPV – JPV Jun 08 '22 at 05:05