How to Split Cell by Comma in Google Sheets (2 Easy Methods)

Google Sheets is a powerful tool that allows users to organize, analyze, and visualize data. One common task when working with data in Google Sheets is splitting cells that contain multiple values separated by a delimiter, such as a comma.

Overview of the article

The above image is the overview of the article split cell by comma in Google Sheets. In this article, you will learn the step-by-step procedure of different methods to split cell by comma in Google Sheets.


2 Suitable Methods to Split Cell by Comma in Google Sheets

There are several methods to split cell by comma in Google Sheets, including using the SPLIT function, and the Text to Columns feature.

Here is a dataset of the First Name,ย Last Name, Gender, and Birth Date of different persons. Now you need to split the cells using the comma. Below are the step-by-step procedures for the methods to split cells by comma in Google Sheets.

Dataset for the article


1. Using SPLIT Function

The SPLIT function is a built-in function in Google Sheets that allows you to split a cell’s contents into multiple cells based on a delimiter. Follow the below steps to split the cell by comma using the SPLIT function.

๐Ÿ“Œ Steps:

  • In the beginning, select cell D5 and enter the following formula.
=SPLIT(B5,",")

Entering the formula using the SPLIT function to split cell by comma in Google Sheets

  • Press Enter and the output will look like the following image.

Result of using the SPLIT function to split cell by comma in Google Sheets

  • Lastly, the output will be similar to the following image if you use the fill-handle tool to copy the formula to the following cells.

The final output of using the SPLIT function to split cell by comma in Google Sheets

Read More: How to Use QUERY with SPLIT Function in Google Sheets


2. Utilizing Text to Columns Feature

Another way to split cells by comma in Google Sheets is by using the Text to Columns feature. This feature allows you to quickly and easily split cells into multiple columns based on a delimiter. Follow the steps below.

๐Ÿ“Œ Steps:

  • First of all, select the whole dataset and select options Data >> Split text to columns.

Selecting options to split cell by comma in Google Sheets

  • After that, a Separator menu will appear. Select Comma from the menu.

Selecting comma as the separator to split cell by comma in Google Sheets

  • Finally, the output will look like the following image.

Final output to use Text to Column feature to split cell by comma in Google Sheets

Read More: How to Split Text to Columns Using Formula in Google Sheets


Similar Readings


How to Split First and Last String of a Cell by Comma in Google Sheets

The LEFT and FIND functions can be used to split the first content of a string that appears before the separator comma. Likewise, you can split the content after the separator comma using the RIGHT, LEN, and FIND functions. Now follow the below steps.

๐Ÿ“Œ Steps:

  • First, select cell D5 and enter the following formula.
=LEFT(B5,FIND(",",B5)-1)

Using functions LEFT and FIND to split cell by comma in Google Sheets

  • Then, press Enter and the first content from cell B5 will be split into cell D5. Use the fill-handle tool to copy the formula to the following cells.

Result of using the Functions LEFT and FIND

  • Similarly, to split the content that appears after the comma, select cell E5 and insert the following formula.
=RIGHT(B5,LEN(B5)-FIND(",",B5))

Entering formula using RIGHT, LEN and FIND

  • After that, press Enter, and the last content from cell B5 will be split into cell E5. Use the fill-handle tool to copy the formula to the following cells.

Final output of splitting first and last string of a cell by comma

Read More: How to Split Text to Columns Based on Line Break in Google Sheets


How to Split Cells Vertically in Google Sheets

You can split the cells vertically using the ARRAYFORMULA, TRANSPOSE, and SPLIT functions. Follow the below steps to split cells vertically in Google Sheets.

๐Ÿ“Œ Steps:

  • First, select cell E5 and input the following formula.
=ARRAYFORMULA(TRANSPOSE(SPLIT(B5:B7,",")))

Entering the formula using ARRAYFORMULA, TRANSPOSE, and FIND functions to split cell vertically

  • Now, hit Enter and the final output will look like the following image.

The final output of the formula using ARRAYFORMULA, TRANSPOSE and FIND functions to split cell vertically

Read More: How to Split String into Array in Google Sheets (3 Easy Methods)


Things to Remember

Input the proper separator to split the cell.


Conclusion

Hopefully, now you can split cell by comma in Google sheets following the above methods. Please comment below with your doubts or suggestions regarding this article. Visit our website, OfficeWheel, for more articles related to Google Sheets.


Related Articles

Jawadul Islam Chowdhury

Jawadul Islam Chowdhury

Hello! This is Jawad. I create Google Spreadsheets-related content for OfficeWheel. I enjoy doing research and solving Google Spreadsheet-related problems. I love to learn new things and teach them to others.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo