I would like to handle numbers in google sheets, I need to insert punctuation in it.

Example:

I need this:

turn this:

Always in the same pattern, I have a list of numbers.

CodePudding user response：

Assuming that the number strings are in column A2:A, use this regex formula:

=arrayformula( 
  iferror( 
    regexreplace( 
      A2:A, 
      "(\d{7})(\d{2})(\d{4})(\d)(\d{2})(\d{4})", 
      "$1-$2.$3.$4.$5.$6"
    ) 
  ) 
)

To learn the exact regular expression syntax used by Google Sheets, see RE2.

If you want to do this with Apps Script, use String.replace() and the same regex parameters as in the spreadsheet formula above. Note that Google Apps Script uses JavaScript regexes.

CodePudding user response：

Try this:

function reorder() {
  const a = ["12345678901234567890","12345678901234567890"];//put all of the strings into an array
  const b = a.map(s => {
    return `${s.slice(0,7)}-${s.slice(7,9)}.${s.slice(9,13)}.${s.slice(13,14)}.${s.slice(14,16)}.${s.slice(16)}`
  })
  Logger.log(b.join('\n'))
}

Execution log
12:34:02 PM Notice  Execution started
12:34:03 PM Info    1234567-89.0123.4.56.7890
                    1234567-89.0123.4.56.7890
12:34:04 PM Notice  Execution completed

Or this:

function reorder() {
  const a = ["12345678901234567890","12345678901234567890"];
  const b = a.map(s => s.replace(/(\w{7})(\w{2})(\w{4})(\w)(\w{2})(\w{4})/, "$1-$2.$3.$4.$5.$6"));
  Logger.log(b.join('\n'))
}

Execution log
12:48:51 PM Notice  Execution started
12:48:52 PM Info    abcdefg-89.0123.4.56.7890
                    1234567-89.0123.4.56.7890
12:48:52 PM Notice  Execution completed

In a spreadsheet:

function reorder() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0")
  const a = sh.getRange(1,1,sh.getLastRow()).getValues().flat();
  const b = a.map(s => [s.replace(/(\w{7})(\w{2})(\w{4})(\w)(\w{2})(\w{4})/, "$1-$2.$3.$4.$5.$6")]);
  sh.getRange(1,2,b.length,b[0].length).setValues(b);
}