I have following code in VBA to achieve this in Excel.
there is column with header and values. in some cells there could be multiple values separated by comma,semicolon or pipe. I wany to split such cells and the new values exept the 1st one paste in the same column so inserting new rows is required. It works but not at 100% ,the if condition checks for delimiters but how to split by the required delimiter as I mentioned above. now it splits just by semicolon. Next step would be if there are other columns too but without multivalues but they need to be copied accordingly. that means you have 3 columns. in the column A you have value a ,in the column B you have some multivalues and in the column C you have value s. so user select the range of cells,in this case in column B then it will be splitted as the code does now but it copies the values from column A and C from the original row as well.
the last requirement is to rewrite the VBA in office scripts to use it in excel online as well.
here is the code I have but no idea how to proceed further
Sub SplitCells()
Dim rng As Range
Set rng = Application.InputBox("Please select a range of cells:", Type:=8)
Dim cell As Range
For Each cell In rng
If InStr(cell.Value, ",") > 0 Or InStr(cell.Value, ";") > 0 Or InStr(cell.Value, "|") > 0 Then
Dim values() As String
values = Split(cell.Value, ";")
Dim numNewRows As Long
numNewRows = UBound(values)
Dim i As Long
For i = numNewRows To 1 Step -1
values(i) = Trim(values(i)) ' Trim the values after splitting
cell.Offset(1).EntireRow.Insert shift:=xlDown
Next i
cell.Resize(numNewRows + 1).Value = Application.Transpose(values)
cell.Resize(numNewRows + 1).EntireColumn.AutoFit ' Optional: adjust column width to fit data
Set cell = cell.Offset(numNewRows) ' Move to last row with split values
End If
Next cell
End Sub
