Split Cell Contents by Space/Newline/Comma/Period in Excel

Listen to this audio for a clear view of this article.

In Excel, you can split cell contents by various delimiters such as space, newline, comma, and period to separate text values into different columns or rows. This can be helpful when you need to extract specific data from a cell or analyze data that is not structured in a user-friendly format. Depending on the delimiter you want to use, you can use different Excel functions or features, such as Text to Columns or formulas like LEFT and RIGHT , and Kutools , to split the cell contents. By splitting cell contents, you can manipulate the data more efficiently and perform analysis on it with greater accuracy.

Split Cell Contents by Space, Newline, Comma, Period

In Excel, you can split cell contents by space, newline, comma, and period to separate text values into different columns or rows. This can be useful when you have data in a single cell that needs to be separated into multiple cells based on certain delimiters.

To split cell contents by space, you can use the Text to Columns feature in Excel. Here’s how :

• Select the cell or cells that you want to split.

Selecting the Data range

• Go to the Data tab on the ribbon and click on the Text to Columns button.

Data > Text to Columns

• In the Convert Text to Columns Wizard , choose delimited as the data type and click Next.
• Select the Space checkbox under Delimiters and click Next.
• Choose the format for the destination cells and click Finish.

Convert Text to Columns Wizard dialog box

Delimiters

Output

To split cell contents by newline, you can use the Text to Columns feature as well. Here’s how :

• Select the cell or cells that you want to split.

Selecting the Data range

• Go to the Data tab on the ribbon and click on the Text to Columns button.
• In the Convert Text to Columns Wizard , choose delimited as the data type and click Next.

Data > Text to Columns

• Select the Other checkbox under Delimiters, type in the character for a newline ( which is usually represented as \n ), and click Next.

Delimiters

• Choose the format for the destination cells and click Finish.

Click Finish

Output

To split cell contents by comma, you can use the built-in function called Text to Columns. Here’s how :

• Select the cell or cells that you want to split.

Selecting the Data range

• Go to the Data tab on the ribbon and click on the Text to Columns button.
• In the Convert Text to Columns Wizard , choose delimited as the data type and click Next.

Data > Text to Columns

• Select the comma checkbox under delimiters and click Next.

Delimiters

• Choose the format for the destination cells and click Finish.

Output

To split cell contents by period, you can use a formula called LEFT and RIGHT. Here’s how :

• Insert a new column to the right of the column containing the cell you want to split.
• In the new column, use the formula =LEFT(A1, FIND(“.”,A1)-1) to extract the text before the first period in the cell.

Applying the formula

Output

• In another new column, use the formula =RIGHT(A1, LEN(A1)-FIND(“.”,A1)) to extract the text after the first period in the cell.

Applying the formula

Output

• Adjust the formulas as necessary for the number of periods in the cell you want to split.

These are some ways to split cell contents by space, newline, comma, and period in Excel.

Split Cell Contents into Multiple Columns or Rows by Space/Comma/Newline with Kutools

To split cell contents into multiple columns or rows by a space, comma, or newline, do as follows:

• In the following image, you can see an example of cell values separated by a comma. Let’s see how to split them into step-by-step instructions.
• You need to select cell values that you want to split into multiple columns or rows.

Data range

• Go to the Kutools tab , select the Merge and Split option, and choose the Split Cells option from the drop-down list.

Selecting the Split Cells option

• It will open the Split Cells dialog box.
• If you want to split the values into multiple columns , then choose the Split to Columns option under the Type section.
• You can also choose the Split to Rows option under the Type section, to split the values into multiple rows.
• Then, you need to choose the separator such as space, newline, semicolon to split the cells.
• You can also specify other delimiters as you wish using the Other text box.
• Hit the OK button.

Split Cells dialog box

• Now, the Split Cells dialog box will pop out to tell you to select a blank cell to display the result.
• Click the OK button.

Select a cell

• Now, the selected cells have been split into multiple columns and rows, as shown in the below image.

Output

Split Text and Numbers Mixed in One Cell/Column into Two Cells/Columns/Rows

If you have text strings mixed with the text and numbers and you need to split them, do as below.

• Let’s consider an example of data as shown in the below image, where you can see the combination of text and numbers.
• You need to select the text strings that contain text and numbers.

Sample text strings

• Go to the Kutools tab, select the Merge and Split option, and choose the Split Cells option from the drop-down list.

Selecting the Split Cells option

• It will open the Split Cells dialog box.
• If you want to split the values into multiple columns , then choose the Split to Columns option under the Type section.
• You can also choose the Split to Rows option under the Type section, to split the values into multiple rows.
• In the Split by section, you need to choose the Text and number option.
• Click the OK button.

Split Cells dialog box

• Then, the Split Cells dialog box will pop out to tell you to select a blank cell to display the result.

Select a blank cell

• Now, the text and numbers are split and displayed in different columns or rows as shown in the below image.

Output

Advantages

Splitting cell contents by space, newline, comma, and period in Excel has several advantages, including:

• #1 Improved data analysis : By splitting cell contents, you can transform unstructured data into structured data, making it easier to analyze and draw insights from. This is especially helpful when working with large datasets that are difficult to analyze without first organizing the data.
• #2 Increased efficiency : Splitting cell contents can help you work more efficiently by allowing you to perform tasks more quickly and accurately. For example, if you need to extract specific data from a cell or column, splitting the contents by a specific delimiter can allow you to do this more easily.
• #3 Enhanced data accuracy : When working with large datasets, it can be difficult to avoid errors when inputting data. Splitting cell contents can help minimize errors by allowing you to organize data more systematically and effectively.
• #4 Flexibility in data presentation : Splitting cell contents allows you to present data in a more user-friendly way by separating it into individual cells or columns. This can help make your data more accessible and easier to understand, especially if you need to share your findings with others.

Overall, splitting cell contents in Excel is a useful feature that can help you better organize, analyze, and present your data.

Verdict

In this article, Splitting cell contents by space, newline, comma, and period in Excel is a useful feature that allows you to separate text values into different columns or rows. This can be helpful when you have data in a single cell that needs to be separated based on certain delimiters. Excel provides different tools and functions to split cell contents, such as Text to Columns, LEFT and RIGHT formulas , and more. By splitting cell contents, you can manipulate the data more efficiently, perform analysis with greater accuracy, and gain insights that might have been hidden in the original data structure. For more articles, you can visit our homepage.

Video Tutorial

Here is the video tutorial to Splitting cell contents by space, newline, comma, and period in Excel for your better understanding.

Splitting cell contents by space, newline, comma, and period in Excel

FAQ

What is the purpose of splitting cell contents in Excel?

The purpose of splitting cell contents in Excel is to separate text values into different columns or rows based on specific delimiters. This can make it easier to analyze data, manipulate it more efficiently, and gain insights that might have been hidden in the original data structure.

How can I split cell contents in Excel by space?

You can split cell contents in Excel by space using the Text to Columns feature. Simply select the cell or cells you want to split, go to the Data tab on the ribbon, click on the Text to Columns button , choose Delimited as the data type , select the Space checkbox under Delimiters , choose the format for the destination cells , and click Finish.

How can I split cell contents in Excel by newline?

You can split cell contents in Excel by newline using the Text to Columns feature. Simply select the cell or cells you want to split, go to the Data tab on the ribbon, click on the Text to Columns button, choose Delimited as the data type , select the Other checkbox under Delimiters , type in the character for a newline , choose the format for the destination cells , and click Finish.

How can I split cell contents in Excel by comma?

You can split cell contents in Excel by comma using the built-in Text to Columns feature. Select the cell or cells you want to split, go to the Data tab on the ribbon, click on the Text to Columns button, choose Delimited as the data type , select the Comma checkbox under Delimiters , choose the format for the destination cells , and click Finish.