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.
Download Practice Workbook
You can download the practice workbook from the following download button.
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.
- 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.
- 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.
- 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.
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.