Paste Comma Separated Values into Excel (in Different Orders)

Method 1 – Paste Comma Separated Values into Excel from a CSV File Directly

Steps:

We have data in a CSV file like the image below:

data in a CSV file

To open a comma-separated CSV file with Excel: Right-mouse click on the CSV file >> Open with >> Excel.

Paste Comma Separated Values into Excel from a CSV File Directly

The comma-delimited data in CSV format after opening with Excel will be separated into multiple columns automatically.
Cell C2 in the red box denotes that the data has been separated into multiple columns.

CSV format after opening with Excel

As we want data in xlsx format, copy the data.

copy the data from a CSV file opened with Excel

Paste it into a blank Excel file and the data will look like the below image:

Paste Comma Separated Values into Excel from a CSV File Directly

Method 2 – Open CSV File in Text Editor, Copy Data, and Paste It into Excel

Steps:

Copy the data from CSV file format by pressing Ctrl + C.

Open CSV File in Text Editor, Copy Data, and Paste It into Excel

Paste the data in cell B4.

pasted the data

All the data will be stored in a single column.

the data will be stored in a single column

We have to convert the data into a table.

Select your data, B4:B12 >> Data >> Data Tools >> Text to Columns.

convert the data into a table by text to column option

Convert Text to Columns Wizard- Step 1 of 3 pop-ups will appear.Mark Delimited and click on Next.

mark the Delimited and click on the Next

Convert Text to Columns Wizard- Step 2 of 3 will appear . Mark the following: Tab, Comma and Space and click on Next.

mark the following: Tab, Comma, and Space and again click on Next

In Convert Text to Columns Wizard- Step 3 of 3, select General and click Finish.

select the General and select Finish

The final data in a separate column will look like this.

the final data in a separate column

Read More: How to Fix CSV File in Excel

How to Convert Comma Separated String to List in Excel

Steps:

Press Alt + F11 to open the Microsoft Visual Basic for Applications window.

How to Convert Comma Separated String to List in Excel

Insert the following code:

In the newly opened module, we will insert the following code

Sub SplitAll()
Dim Yrng As Range
Dim Yrng1 As Range
Dim YCell As Range
Dim A As Long
Dim YAdrs As String
Dim YUp As Boolean
Dim YRet As Variant
On Error Resume Next
YAdrs = Application.ActiveWindow.RangeSelection.address
Set Yrng = Application.InputBox("Select a single column", "Pop-Up", YAdrs, , , , , 8)
Set Yrng = Application.Intersect(Yrng, Yrng.Worksheet.UsedRange)
If Yrng Is Nothing Then Exit Sub
If Yrng.Columns.Count > 1 Then
MsgBox "Don't select more than one column!", , "Pop-Up"
Exit Sub
End If
Set Yrng1 = Application.InputBox("Select list destination:", "Pop-Up", , , , , , 8)
Set Yrng1 = Yrng1.Range("A1")
If Yrng1 Is Nothing Then Exit Sub
YUp = Application.ScreenUpdating
Application.ScreenUpdating = False
For Each YCell In Yrng
YRet = Split(YCell.Value, ",")
Yrng1.Worksheet.Range(Yrng1.Offset(A, 0), Yrng1.Offset(A + UBound(YRet, 1), 0)) = Application.WorksheetFunction.Transpose(YRet)
A = A + UBound(YRet, 1) + 1
Next
Application.ScreenUpdating = YUp
End Sub