**Excel** is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes, we need to **split data with commas into columns**. In Excel, to split data into columns by comma, we can apply various methods. In this article, I am going to show you **8** effective methods in Excel to **split data** into **columns** by **comma.**

**Table of Contents**hide

## Download Practice Workbook

This is the **dataset** that I am going to use. Here we have some people along with their **Addresses**. The Addresses have commas, we will split the **Town** and **Country** into **separate columns** in this article.

## 7 Methods to Split Data into Columns by Comma in Excel

### 1. Split Data into Columns Using Text to Column Feature

First, I will show you how to use the **Text to Column** feature to split data into **multiple columns**.

**STEPS:**

- First, select
**C5:C11**. Then, go to the**Data**tab >> select**Data Tools**>> select**Text to Columns**

**Convert Text to Column Wizard**will appear. Choose the**Delimited**Then click**Next**.

- Next, choose the
**Delimiter**as**Comma**. Then click**Next**.

- Then choose
**General**as**Column Data Format**. Select the**Destination**. Finally, select**Finish**.

**Excel** will split the data.

**Read More: How to Split Data into Multiple Columns in Excel**

### 2. Applying Flash Fill to Split Data in Excel

Now, I will use **Flash Fill** to **split data** in **Excel**.

**STEPS:**

- Write
**Tokyo**in**D5**.

- Use
**Fill Handle**to**AutoFill**up to**D11**.

- Now click the
**Auto Fill Options**(see image)

- Choose
**Flash Fill**.

**Excel** will show the **cities**.

- Similarly, separate the
**Country**.

**Read More: How to Split Data in One Excel Cell into Multiple Columns (5 Methods)**

### 3. Using a Combination of LEFT, FIND & LEN to Split Data into Columns by Comma

In this section, I will explain how you can **split data** using **the** **LEFT**, **FIND**, and **LEN** **functions**.

**STEPS:**

- Go to
**D5**. Write down the following formula.

`=LEFT(C5,FIND(",",C5)-1)`

**Formula Breakdown**

**FIND(â€ś,â€ť,C5)** âž¤ Returns the position of a character **comma (,)** in **C5**.**Output**: **6**

**LEFT(C5,FIND(â€ś,â€ť,C5)-1) **âž¤ Returns the **specified number** from the starting of **text** in **C5**.**Output**: **Tokyo**

- Then, press
**ENTER**.**Excel**will return the output.

- Now, use the
**Fill****Handle**to**AutoFill**.

For separating the **Country**,

- Go to
**E5**. Write down the following formula.

`=RIGHT(C5,LEN(C5)-FIND(",",C5))`

**Formula Breakdown**

**FIND(â€ś,â€ť,C5)** âž¤ Returns the position of **comma(,)** in **C5**.**Output:** **6**

**LEN(C5)** âž¤ Returns the **number** of **characters **in **C5**.**Output:** **11**

**RIGHT(C5,LEN(C5)-FIND(â€ś,â€ť,C5)) **âž¤ Returns the **specified position** of a **character** from the end of **C5**.**Output**: **Japan**

- Now, press
**ENTER**.**Excel**will show the output.

- Now, use the
**Fill****Handle**to**AutoFill**.

### 4. Use of PowerQuery to Split Data

Now I will use **PowerQuery** to **split data** into **columns** in **Excel**.

**STEPS:**

- Create a
**table**To do so, select the entire**range B4:C11**. - Press
**CTRL + T**. An**input box**will appear. Put the**data**in your table. Here it is**B4:C11**.

- Now, go to the
**Data**tab >> select**From Table/Range**.

**PowerQuery Editor**window will pop up. Keep the**cursor**on the**Address column**. Then__right click your__**mouse****Context Bar**.- From the
**Context Bar**, select**Split Column**>> select**By Delimiter**

**Split Column by Delimiter**dialog box will appear. Select the**Delimiter**as**Comma**. Then click**OK**.

**Excel**will**split**the**column**under**1**and**Address.2 column**. Then click**Close & Load**.

**Excel**will transfer the**dataset**into a**new worksheet**.

**Rename**the**column**.

**Read More: How to Split Data in Excel (5 Ways)**

### 5. Converting the Data into CSV File

Now, I will show another method. I will convert the **dataset** into a **CSV** (**comma-separated values**) file first.

**STEPS:**

- First,
**copy**the column**Address**into a**Notepad****page**.

- Then, go to
**File**>> select**Save As**.

- Now,
**set the name**and**save the file**. Remember,**you have to put the .csv suffix in the name.**

- Now, open the
**file**from**the location**where you.__saved it earlier__

**Excel**will split the**data**.

- Now,
**format**as you wish.

### 6. Use of VBA to Split Data into Columns by Comma

Now, I will use a **VBA code** to **split data**.

**STEPS:**

- Press
**ALT + F11**to open the**VBA window**. - Then go to
**Insert**>> select**Module**.

- A
**new module**will open. Write down the following code.

```
Sub SplitColumn()
Dim SplitData() As String, Count As Long, i As Variant
For n = 5 To 11
SplitData = Split(Cells(n, 3), ",")
Count = 4
For Each i In SplitData
Cells(n, Count) = I
Count = Count + 1
Next i
Next n
End Sub
```

**Code Breakdown**

- Here, I have created a
**Sub Procedure**. I used*SplitColumn***the dim statement**to define a**variable****SplitData**as**String**and**i**as a**variant**. - Then I used a
**For Loop**.**5 to 11**denotes that I will**split**the**data**from the**5th to 11th row**. - Next, I used the
**VBA Split**function where**n**is the**row**number and**3**defines that the**data**are in the**C****column**. As**Count = 4**, the**data**will be**split**into**column D**. - Again, I used a
**For Loop**to**increment**the**Count**.

- Now press
**F5**to run the**code**.**Excel**will**split**the**data**.

### 7. Using the FILTERXML, SUBSTITUTE & TRANSPOSE Functions in Excel to Split Data

Now I am going to use **the FILTERXML function** along with **the SUBSTITUTE** & **TRANSPOSE** functions. This will work for upgraded versions of **Excel**.

**STEPS:**

Select **D5** and **E5**. Write down the following formula

`=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(C5,",","</s><s>")& "</s></t>","//s"))`

Â **Formula Breakdown**

**SUBSTITUTE(C5,â€ť,â€ť,â€ť</s><s>â€ť) âž¤ **This will substitute the **comma (,)** in the **D5** and **E5**.**Output:** **â€śTokyo</s><s>Japanâ€ť****Â **

**FILTERXML(â€ś<t><s>â€ť&SUBSTITUTE(C5,â€ť,â€ť,â€ť</s><s>â€ť)& â€ś</s></t>â€ť,â€ť//sâ€ť) âž¤ **It returns **XML data** from the **content** following **XPath
Output:**

**{â€śTokyoâ€ť;â€ťJapanâ€ť}**

**TRANSPOSE(FILTERXML(â€ś<t><s>â€ť&SUBSTITUTE(C5,â€ť,â€ť,â€ť</s><s>â€ť)& â€ś</s></t>â€ť,â€ť//sâ€ť)) âž¤ **It will transpose the array.**Output:** **{â€śTokyoâ€ť,â€ťJapanâ€ť}**

- Then press
**ENTER**.**Excel**will return the outputs.

- Then use
**Fill Handle**to**AutoFill**.

## Practice Workbook

Practice makes a man perfect. It is important to practice to internalize any method. Thatâ€™s why I have attached a **practice sheet** for you.

## Conclusion

In this article, I have demonstrated **7** effective methods in **Excel** to **split data** into **columns** by **comma**. I hope it helps everyone. And lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.

## Related Articles

**How to Split Data into Equal Groups in Excel (3 Methods)****Excel Macro to Split a Cell into Multiple Rows (With Easy Steps)****How to Split Data from One Cell into Multiple Rows in Excel (3 Methods)****Excel Macro to Split Data into Multiple Files (With Simple Steps)****How to Split Data into Multiple Worksheets in Excel**