# How to split text by line break in Excel

Use CHAR(10), the line break character, as the delimiter: =TEXTSPLIT(A2,CHAR(10)) in Excel 365, =SPLIT(A2,CHAR(10)) in Google Sheets.

**Excel:** `=TEXTSPLIT(A2,CHAR(10))` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=SPLIT(A2,CHAR(10))`

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Mailing address | Line 1 | Line 2 | Line 3 |
| Northwind Traders 1420 Lake St Minneapolis, MN 55401 |  |  |  |
| Contoso Ltd 88 Oak Ave Saint Paul, MN 55102 |  |  |  |
| Fabrikam Inc 7 Elm Ct Madison, WI 53703 |  |  |  |
| Litware 305 River Rd Fargo, ND 58102 |  |  |  |
| Adatum Corp 12 Pine Ln Duluth, MN 55802 |  |  |  |

Formula in B2, filled to B6: `=TEXTSPLIT(A2,CHAR(10))`

Result: Northwind Traders; Contoso Ltd; Fabrikam Inc; Litware; Adatum Corp

## How it works

A line break typed with Alt+Enter is the character with code 10, so `CHAR(10)` is the delimiter. TEXTSPLIT spills the lines to the right: the company in B, the street in C and the city in D.

## Other ways

- `=LEFT(A2,FIND(CHAR(10),A2)-1)`: Works in every version, for the first line only. Use MID with FIND for the others.

## Common mistakes

- Text from Windows files can have a carriage return before each line break (CHAR(13) then CHAR(10)). The extra character is invisible but breaks lookups; split on `CHAR(13)&CHAR(10)` or clean with CLEAN.
- Wrapped text without real line breaks doesn't split: the wrapping is only on screen.

## Questions

**How do I split the lines into rows instead?**

Use the row delimiter: `=TEXTSPLIT(A2,,CHAR(10))`.

**How do I replace the line breaks with commas?**

Use `=SUBSTITUTE(A2,CHAR(10),", ")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/split-text-by-line-break