I hope herewith I was able to explain clearly enough how it is working. The delimited text will be copied to the workbooks first column in text. In the last step of the query, this table is simply expanded, thus duplicating the rows of the columns preceding the custom column. Split-up any delimited file into file parts of equal size or on column values.
To transpose the lists of both columns, which you actually required to do Load or convert semicolon-formatted CSV into Excel XLSX.You can also do that in 3 steps via add custom column and use the M-functions. I selected just Tab and Comma but could also select Space to get rid of the extra space. It is not too difficult what I did, though I nested a couple of functions into one. Select the column that you want to split: Then go up to Data -> Text to Columns: You’ll get the following window: This window gives you several options for splitting the cells, using commas, spaces, semicolons, tabs, other, etc. StrSMSID = Left(StrSMSID, Len(StrSMSID) - Len(ActiveCell.Offset(, 11)) - 2) In the Convert Text to Columns Wizard, select Delimited >. Trim(Right((StrSMSID, ",", sSplitSub), Len(StrSMSID))) Select the cell or column that contains the text you want to split. StrPercent = Left(StrPercent, Len(StrPercent) - Len(ActiveCell.Offset(, 10)) - 2) Trim(Right((StrPercent, ",", pSplitSub), Len(StrPercent))) Set CopyRng = Range("A" & RowIndex & ":J" & RowIndex)ĬopyRng.Copy Destination:=ThisWorkbook.Worksheets("Output").Range("A2") LastRow = Cells(Rows.Count, 1).End(xlUp).Row However, you can perform this task using the Left, Mid or Right functions, combined with other built-in Excel.
I think you will have to parse the comma separated values to separate cells for this to work. at the first space), there is no built-in Excel function to do this. Excel needs the DD list items in individual cells and you specify a range instead of a cell as the source. If you want to split an excel text string at the first occurrence of a specified character, (e.g. Dim Events As Integer, LenWODelim As Integer, LenFull As Integer, InsertRow As Integerĭim StrPercent As String, StrSMSID As String, pSplitSub As String, sSplitSub As String Split a String at the First Occurrence of a Specified Character.