Google Sheets Split Text to Column by First Text character

Question

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

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

Answer 1

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);
}