Split Cell Contents by Space/Newline/Comma/Period in Excel
Tags: kutools, splitcells
Published: 2023-04-03 09:36:40

---
title: Split Cell Contents by Space/Newline/Comma/Period in Excel
published: true
date: 2023-04-03 04:21:39 UTC
tags: Kutools,SplitCells
canonical_url: https://aawexcel.com/split-cell-contents-by-space-newline-comma-period-in-excel/
---

<audio controls src="https://aawexcel.com/wp-content/uploads/2023/04/Split-Cell-Contents-by-Space_Newline_Comma_Period-in-Excel-1.wav"></audio>
_Listen to this audio for a clear view of this article._

In [Excel](https://www.microsoft.com/en-us/microsoft-365/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.

![](https://aawexcel.com/wp-content/uploads/2023/04/Split-cells-data-range-2.webp)
_Selecting the Data range_

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

![](https://aawexcel.com/wp-content/uploads/2023/04/Split-cells-1024x548.webp)
_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**.

![](https://aawexcel.com/wp-content/uploads/2023/04/Show-cells-2.webp)
_Convert Text to Columns Wizard dialog box_

![](https://aawexcel.com/wp-content/uploads/2023/04/Split-cells-1.webp)
_Delimiters_

![](https://aawexcel.com/wp-content/uploads/2023/04/Split-cells-3.webp)
_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.

![](https://aawexcel.com/wp-content/uploads/2023/04/Split-cells-data-range-3.webp)
_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**.

![](https://aawexcel.com/wp-content/uploads/2023/04/Split-cells-2-1024x548.webp)
_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**.

![](https://aawexcel.com/wp-content/uploads/2023/04/Split-cells-4.webp)
_Delimiters_

- Choose the **format** for the destination cells and click **Finish**.

![](https://aawexcel.com/wp-content/uploads/2023/04/Split-cells-5.webp)
_Click Finish_

![](https://aawexcel.com/wp-content/uploads/2023/04/Split-cells-6.webp)
_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.

![](https://aawexcel.com/wp-content/uploads/2023/04/Split-cells-data-range-4.webp)
_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**.

![](https://aawexcel.com/wp-content/uploads/2023/04/Split-cells-7-1024x548.webp)
_Data > Text to Columns_

- Select the **comma** checkbox under delimiters and click **Next**.

![](https://aawexcel.com/wp-content/uploads/2023/04/Split-cells-7-1.webp)
_Delimiters_

- Choose the **format** for the destination cells and click **Finish**.

![](https://aawexcel.com/wp-content/uploads/2023/04/Split-cells-data-range-5.webp)
_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.

![](https://aawexcel.com/wp-content/uploads/2023/04/Split-cells-8.webp)
_Applying the formula_

![](https://aawexcel.com/wp-content/uploads/2023/04/Split-cells-9.webp)
_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.

![](https://aawexcel.com/wp-content/uploads/2023/04/Split-cells-10.webp)
_Applying the formula_

![](https://aawexcel.com/wp-content/uploads/2023/04/Split-cells-11.webp)
_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.

![Example data](https://aawexcel.com/wp-content/uploads/2020/10/Example-data-1.png)
_Data range_

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

![Select the Split Cells option](https://aawexcel.com/wp-content/uploads/2020/10/Select-the-Split-Cells-option.png)
_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](https://aawexcel.com/wp-content/uploads/2020/10/Split-Cells-dialog-box.png)
_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](https://aawexcel.com/wp-content/uploads/2020/10/Select-a-cell.png)
_Select a cell_

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

![Output](https://aawexcel.com/wp-content/uploads/2020/10/Output.png)
_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](https://aawexcel.com/wp-content/uploads/2020/10/Sample-text-strings.png)
_Sample text strings_

- Go to the **[Kutools](https://aawexcel.com/kutools/)** tab, select the **Merge and Split** option, and **choose the**   **Split Cells** option from the drop-down list.

![Select the Split Cells option](https://aawexcel.com/wp-content/uploads/2020/10/Select-the-Split-Cells-option.png)
_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](https://aawexcel.com/wp-content/uploads/2020/10/1-1.png)
_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](https://aawexcel.com/wp-content/uploads/2020/10/2-1.png)
_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](https://aawexcel.com/wp-content/uploads/2020/10/3-1.png)
_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](https://aawexcel.com/).

## Video Tutorial

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

<video controls src="http://aawexcel.com/wp-content/uploads/2023/04/Split-cells.webm"><track src="http://aawexcel.com/wp-content/uploads/2023/03/no-captions.vtt" srclang="en" label="English" kind="subtitles"></track></video>
_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**.