Separate text in once column and move it to another, Excel Scripts

Question

I am trying to write a script in excel that will sperate the date and time value in one column and place the date into a separate column. So for example, I have "11/4/2023 11:00 am" in column A1 when ideally I need the date in A1 and the time in B1; and I need this to loop through every used row. I have tried a couple methods but keep getting errors. I am using scripts to try and automate this. (I have the macro written and working but the software I am using to automate this does not accept macros)

One thing that I have tried

let s = mainSheet.getRange("A2").getValue().toString()
let ss = s.split(" ")
console.log(ss)

The console return value is "45231"

Answer 1

Option 1: applies different datetime formats to Columns A and B in order to extract the date and time.

If Option 1 doesn't meet your requirements, please consider using Option 2.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    selectedSheet.getRange("B1").copyFrom(selectedSheet.getRange("A:A"), ExcelScript.RangeCopyType.all, false, false);
    selectedSheet.getRange("A:A").setNumberFormatLocal("m/d/yyyy");
    selectedSheet.getRange("B:B").setNumberFormatLocal("h:mm AM/PM");
}

---

Option 2: Split text into two parts

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let lastCell = selectedSheet.getRange("A:A").getLastCell().getRangeEdge(ExcelScript.KeyboardDirection.up);
    let dataRange = selectedSheet.getRange("A1:A" + (lastCell.getRowIndex()+1));
    let dataTexts = dataRange.getTexts();
    let resRange = dataRange.getResizedRange(0,1);
    let resTexts = resRange.getTexts();
    for(let i=0; i<dataTexts.length; i++){
      resTexts[i] = splitDateAndTime(dataTexts[i][0])
    }
  resRange.setValues(resTexts);    
  selectedSheet.getRange("A:A").setNumberFormatLocal("m/d/yyyy");
}

function splitDateAndTime(dateTimeString: string): [string, string] {
  const dateTimeRegex = /^(\d{1,2}\/\d{1,2}\/\d{4})\s+(.+)/;
  const match = dateTimeString.match(dateTimeRegex);
  if (match) {
    const datePart = match[1];
    const timePart = match[2];
    return [datePart, timePart];
  }
  return [dateTimeString, ""];
}

If you don't need to validate the datetime string, splitDateAndTime could be simplified as below.

function splitDateAndTime(dateTimeString: string): [string, string] {
  const parts = dateTimeString.split(" ");
  if(parts.length === 3){
    return [parts[0], parts.slice(1,).join(" ")];
  }
  return [dateTimeString, ""];
}