# How to Split Cells by Line Break in Excel Using Formulas?

Have you ever encountered an **Excel spreadsheet** where multiple values are stored in a single cell, separated by line breaks? Trying to work with data in this format can be frustrating and time-consuming. Fortunately, Excel provides several methods to **split cells by line break**, allowing you to easily separate the data into individual cells. In this article, we’ll focus on using **formulas** to split cells by line break.

## Understanding Line Breaks in Excel

Before we dive into the formulas, let’s clarify what we mean by **“line breaks” in Excel**. A line break, also known as a carriage return or newline, is a special character that signifies the end of a line of text. In Excel, line breaks are represented by the character combination `ALT+ENTER`

or `CHAR(10)`

.

When you have a cell containing multiple values separated by line breaks, it may look something like this:

Original Cell |
---|

John\nSmith\n123 Main St\nNew York |

Our goal is to split this cell into separate cells, with each value occupying its own cell.

## Using the TRIM and SUBSTITUTE Functions

One way to **split cells by line break** is to use a combination of the `TRIM`

and `SUBSTITUTE`

functions. The `TRIM`

function removes leading, trailing, and extra spaces from a text string, while the `SUBSTITUTE`

function replaces specified characters with another character.

Here’s the formula to split a cell by line break:

`=TRIM(SUBSTITUTE(A1,CHAR(10)," "))`

In this formula:

`A1`

represents the cell containing the original data with line breaks.`CHAR(10)`

represents the line break character.- The
`SUBSTITUTE`

function replaces the line breaks with spaces. - The
`TRIM`

function removes any extra spaces, leaving only single spaces between the values.

To use this formula:

- Select the cell where you want the split data to start.
- Enter the formula
`=TRIM(SUBSTITUTE(A1,CHAR(10)," "))`

, replacing`A1`

with the reference to your original cell. - Press
`ENTER`

to see the result.

The **split values** will appear in separate cells, with each value occupying one cell.

### Breaking Down the TRIM and SUBSTITUTE Formula

Let’s take a closer look at how the `TRIM`

and `SUBSTITUTE`

functions work together to split cells by line break.

`SUBSTITUTE(A1,CHAR(10)," ")`

: This part of the formula replaces all line break characters (`CHAR(10)`

) in cell A1 with spaces. If cell A1 contains “John\nSmith\n123 Main St\nNew York”, the result of this step would be “John Smith 123 Main St New York”.`TRIM(...)`

: The`TRIM`

function then removes any leading, trailing, and extra spaces from the result of the previous step. This ensures that there are no unwanted spaces between the values. The final result would be “John Smith 123 Main St New York”.

By combining these two functions, we can effectively split the values separated by line breaks into a single cell, with each value separated by a space.

## Using the FILTERXML Function

Another powerful way to **split cells by line break** is using the `FILTERXML`

function. This function allows you to extract data from a string using XPath-like syntax.

Here’s the formula to split a cell by line break using `FILTERXML`

:

`=FILTERXML("<t><s>"&SUBSTITUTE(A1,CHAR(10),"</s><s>")&"</s></t>","//s")`

In this formula:

`A1`

represents the cell containing the original data with line breaks.`CHAR(10)`

represents the line break character.- The
`SUBSTITUTE`

function replaces the line breaks with the closing and opening tags`</s><s>`

. - The resulting string is wrapped with
`<t>`

and`</t>`

tags to create a valid XML structure. - The XPath expression
`//s`

selects all the`<s>`

elements within the XML structure.

To use this formula:

- Select the cell where you want the split data to start.
- Enter the formula
`=FILTERXML("<t><s>"&SUBSTITUTE(A1,CHAR(10),"</s><s>")&"</s></t>","//s")`

, replacing`A1`

with the reference to your original cell. - Press
`CTRL+SHIFT+ENTER`

to enter the formula as an**array formula**. Excel will automatically enclose the formula in curly braces`{}`

. - The split values will appear in separate cells below the formula cell.

Note that the `FILTERXML`

function is available in **Excel 2013 and later versions**. If you’re using an earlier version, you can use the `TRIM`

and `SUBSTITUTE`

method instead.

### Understanding the FILTERXML Formula

The `FILTERXML`

function may seem complex at first glance, but let’s break it down step by step to understand how it works.

`SUBSTITUTE(A1,CHAR(10),"</s><s>")`

: This part of the formula replaces all line break characters (`CHAR(10)`

) in cell A1 with the closing and opening tags`</s><s>`

. If cell A1 contains “John\nSmith\n123 Main St\nNew York”, the result of this step would be “John~~Smith~~~~123 Main St~~~~New York”.~~`"<t><s>"&...&"</s></t>"`

: The resulting string from the previous step is then wrapped with`<t>`

and`</t>`

tags to create a valid XML structure. The final XML string would look like this:

` <t><s>John</s><s>Smith</s><s>123 Main St</s><s>New York</s></t>`

`FILTERXML(...,"//s")`

: The`FILTERXML`

function is then used to extract data from the XML string using the XPath expression`//s`

. This expression selects all the`<s>`

elements within the XML structure. The result is an array of values, with each value in a separate cell.

By leveraging the power of XML and XPath, the `FILTERXML`

function provides a more flexible and robust way to split cells by line break compared to the `TRIM`

and `SUBSTITUTE`

method.

## Handling Multiple Columns

If you have multiple columns containing cells with line breaks, you can easily **apply the formulas to the entire column**. Simply select the cell with the formula, and then double-click the small square at the bottom-right corner of the cell (known as the fill handle). Excel will automatically copy the formula down the column, adjusting the cell references accordingly.

For example, if your original data is in column A, and you enter the `TRIM`

and `SUBSTITUTE`

formula in cell B1, double-clicking the fill handle will copy the formula down column B, splitting the data for each cell in column A.

### Splitting Multiple Columns with a Single Formula

In some cases, you may want to split multiple columns simultaneously using a single formula. This can be achieved by combining the splitting formulas with Excel’s built-in functions like `INDEX`

and `COLUMNS`

.

Here’s an example of how to split cells in multiple columns using the `TRIM`

and `SUBSTITUTE`

formula:

`=TRIM(SUBSTITUTE(INDEX($A$1:$C$1,1,COLUMNS($A$1:A1)),CHAR(10)," "))`

In this formula:

`$A$1:$C$1`

represents the range of cells containing the original data with line breaks (in this example, we assume the data is in columns A to C).`INDEX($A$1:$C$1,1,COLUMNS($A$1:A1))`

retrieves the value from the corresponding column based on the position of the formula cell.- The rest of the formula works the same way as the single-cell version, replacing line breaks with spaces and trimming extra spaces.

To use this formula:

- Select the cell where you want the split data to start (let’s say cell D1).
- Enter the formula
`=TRIM(SUBSTITUTE(INDEX($A$1:$C$1,1,COLUMNS($A$1:A1)),CHAR(10)," "))`

. - Press
`ENTER`

to see the result. - Drag the formula across the columns and down the rows to split the data for all cells in the original range.

By using the `INDEX`

and `COLUMNS`

functions, we can dynamically reference the appropriate cell based on the position of the formula, allowing us to split multiple columns with a single formula.

## Splitting Cells with a Specific Delimiter

Sometimes, instead of line breaks, you may have cells containing values separated by a **specific delimiter**, such as a comma or semicolon. In such cases, you can easily modify the formulas to split the cells based on the delimiter.

To split cells by a specific delimiter, replace `CHAR(10)`

in the formulas with the delimiter character enclosed in double quotes. For example, if your values are separated by commas, you would use `","`

instead of `CHAR(10)`

.

Here’s the modified `TRIM`

and `SUBSTITUTE`

formula:

`=TRIM(SUBSTITUTE(A1,",", " "))`

And here’s the modified `FILTERXML`

formula:

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

These modified formulas will split the cells based on the specified delimiter instead of line breaks.

### Handling Multiple Delimiters

In some situations, your data may contain multiple delimiters, such as a combination of commas and semicolons. To split cells with multiple delimiters, you can nest multiple `SUBSTITUTE`

functions within the formula.

Here’s an example of how to split cells with multiple delimiters using the `TRIM`

and `SUBSTITUTE`

formula:

`=TRIM(SUBSTITUTE(SUBSTITUTE(A1,",", " "),";", " "))`

In this formula, the `SUBSTITUTE`

function is used twice:

`SUBSTITUTE(A1,",", " ")`

replaces all commas with spaces.`SUBSTITUTE(...,";", " ")`

replaces all semicolons with spaces.

The `TRIM`

function then removes any extra spaces, resulting in the split values separated by single spaces.

You can adapt this approach to handle any number of delimiters by nesting additional `SUBSTITUTE`

functions within the formula.

## Final Thoughts

**Splitting cells by line break** in Excel can greatly simplify your data analysis and manipulation tasks. By using formulas like `TRIM`

, `SUBSTITUTE`

, and `FILTERXML`

, you can easily separate values stored in a single cell into individual cells. These formulas provide a quick and efficient way to **reorganize your data** without the need for manual copy-pasting or complex VBA macros.

Remember to adjust the formulas based on your specific needs, such as handling different delimiters or referencing the appropriate cell ranges. You can also combine these formulas with other Excel functions to handle more complex scenarios, such as splitting multiple columns simultaneously or dealing with inconsistent data.

## FAQs

### What is a line break in Excel?

### How do I split cells by line break using the TRIM and SUBSTITUTE functions?

`=TRIM(SUBSTITUTE(A1,CHAR(10)," "))`

. Replace A1 with the reference to your original cell containing the line breaks.### What is the FILTERXML function and how can it be used to split cells by line break?

`=FILTERXML("<t><s>"&SUBSTITUTE(A1,CHAR(10),"</s><s>")&"</s></t>","//s")`

. Replace A1 with the reference to your original cell containing the line breaks. Note that the FILTERXML function is available in Excel 2013 and later versions.### Can I apply the cell splitting formulas to multiple columns at once?

### How can I split cells by a specific delimiter instead of line breaks?

`=TRIM(SUBSTITUTE(A1,","," "))`

. The modified FILTERXML formula would be: `=FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s")`

.Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.