Paste Comma Separated Values into Excel (in Different Orders)

While working in Excel we have to deal with a comma or space-separated data from a different source. Commonly, the file in CSV format has many delimited values like commas or space. When we import data from them into an Excel file, we can easily change the data into a beautiful array. In this article, we will demonstrate several easy and time-saving methods to paste comma-separated values into Excel.

Table of Contents hide

Download Practice Workbook

2 Simple Methods to Paste Comma Separated Values into Excel

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

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

How to Convert Comma Separated String to List in Excel

Conclusion

Download Practice Workbook

You can download the practice workbook from the following download button.

Paste Comma Separated Values.xlsm

2 Simple Methods to Paste Comma Separated Values into Excel

We have a sample CSV file having a record of different hurricane occurrences. Our sample data have three different columns of Month, Average, and Years from 2005-2015.

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

Suppose, we have some data that are comma-separated in a CSV file. Now we are required to split the data into different columns. In the following method, we are going to demonstrate how to paste comma-separated values into Excel.

📌 Steps:

Imagine, we have data in a CSV file like the image below:

Now, we will follow the steps to open a comma-separated CSV file with Excel: Right-mouse click on the CSV file >> Open with >> Excel.

Note that comma-delimited data in CSV format after opening with Excel has been separated into multiple columns automatically.
After that just look at the below image where we marked the file in CSV format message in red mark.
And a cell C2 in the red box denotes that the data has been separated into multiple columns.

As we want data in xlsx format, we will copy the data from a CSV file opened with Excel like the image below:

At this moment we will paste it into a blank Excel file and the data will look like the below image:

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

In this method, we will demonstrate another process of how a comma-separated dataset can be pasted into Excel.

📌 Steps:

Firstly, we will copy the data from CSV file format by pressing Ctrl + C.

Secondly notice that in cell B4, we have pasted the data just like the image below.

After pasting the data, all the data will be stored in a single column.

Now we have to convert the data into a table. To do so, we will go through the steps: select your data in this case B4:B12 >> Data >> Data Tools >> Text to Columns.

Convert Text to Columns Wizard- Step 1 of 3 pop-ups will appear. Now, we will mark the Delimited and click on Next.

After pressing on Next, Convert Text to Columns Wizard- Step 2 of 3 will appear and in this pop-up, we will mark the following: Tab, Comma, and Space and again click on Next.

Again, in the pop-up of Convert Text to Columns Wizard- Step 3 of 3 we will select the General and select Finish.

Now, the final data in a separate column will look like this.

How to Convert Comma Separated String to List in Excel

Now, we will see how to convert comma-separated strings to a list after getting them pasted into Excel. Follow the steps below.

📌 Steps:

After pressing Alt + F11 keys, the Microsoft Visual Basic for Applications window will appear.

Now, in the newly opened module, we will insert the following code just like the image below:

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

After pressing F5 or Run a Pop-Up will appear like the image given below. At this moment, in Select a single column we will insert $B$5: $B$10 and then click OK.

After that, a new pop-up will appear and there, we will insert a cell for the destination in this case that is C5.

And the resulting image will be the below image.

Conclusion

Follow these steps and stages on the topic to paste comma-separated values into Excel. Moreover, you are welcome to download the workbook and use it for your own practice. If you have any questions, concerns, or suggestions, please leave them in the comments section of our blog ExcelDemy.