Most people know you can split text in Excel using commas or spaces, but a line break delimiter is a different beast entirely. If you've ever pasted data where multiple values are stacked inside a single cell, you'll know exactly how frustrating it is to work with. The good news is Excel has solid tools to handle this, once you know where to look.
What Is a Line Break Inside an Excel Cell?
When you press Alt + Enter inside a cell, Excel inserts a line break. The cell can hold multiple lines of text, and if you have wrap text turned on, you'll actually see each line displayed separately in that single cell.
Behind the scenes, that line break is stored as a character. In Excel's formula language, it's represented as CHAR(10), which is the ASCII code for a newline character. Knowing this is the key to splitting or cleaning that data.
Tip: On a Mac, the line break character is also CHAR(10) in Excel formulas, so the approach works the same way across platforms.
Why This Comes Up So Often
Data pasted from web pages, exported reports, or copied from tools like Notion or Google Docs often lands in Excel with line breaks crammed into single cells. You'll see a cell that looks like it contains an address on three lines, or a list of tags separated by enters.
This makes sorting, filtering, and analysis nearly impossible until you deal with those breaks. You have a couple of good options depending on what you need.
Method 1: Text to Columns with CHAR(10)
Excel's Text to Columns feature can split a cell using a line break as the delimiter. Here's how to do it step by step.
- Select the column or cells you want to split.
- Go to Data in the ribbon, then click Text to Columns.
- Choose Delimited and click Next.
- In the delimiter options, check Other and then press Ctrl + J in the text box next to it. This inserts the line break character (you won't see anything visible, but it's there).
- Click Finish and your data splits across columns.
The Ctrl + J trick is the bit most people don't know. It's the keyboard shortcut to enter CHAR(10) into that small input field, and it works reliably in both older and newer versions of Excel.
Method 2: Using CHAR(10) in Formulas
If you want to use formulas instead, CHAR(10) lets you both find and replace line breaks programmatically. A common use case is replacing line breaks with another delimiter like a comma or pipe character.
You can use the SUBSTITUTE function like this:
=SUBSTITUTE(A1, CHAR(10), ", ")
This replaces every line break in cell A1 with a comma and a space, turning a multi-line cell into a clean single-line comma-separated string. For the output to look right, make sure wrap text is turned off on the result cells.
Method 3: Find and Replace
You can also use Excel's Find and Replace dialog to clean up line breaks across a whole sheet quickly.
- Press Ctrl + H to open Find and Replace.
- In the Find what box, press Ctrl + J (same trick as above).
- In the Replace with box, type whatever delimiter you want, such as a comma or a space.
- Click Replace All.
This is the fastest method when you just want to clean data without writing any formulas. If you need to process data further, you might also find the online delimiter converter useful for quick transformations outside of Excel.
Quick Reference: Methods Compared
| Method | Best For | Keeps Original Data? |
|---|---|---|
| Text to Columns | Splitting into separate columns | No (overwrites) |
| SUBSTITUTE + CHAR(10) | Formula-based transformation | Yes (uses new cell) |
| Find and Replace | Bulk cleanup across a sheet | No (overwrites) |
Common Mistakes to Avoid
- Forgetting to turn on wrap text before checking if line breaks exist in a cell.
- Using a visible character in the Find what box instead of Ctrl + J, which won't match the actual line break.
- Not accounting for CHAR(13) on Windows line endings. If data came from a Windows text file, you might need to substitute both CHAR(13) and CHAR(10).
- Applying Text to Columns without backing up your data first, since it overwrites in place.
Key Points
- Line breaks inside Excel cells are represented by CHAR(10) in formulas.
- Press Ctrl + J to enter the line break character in Text to Columns or Find and Replace dialogs.
- The SUBSTITUTE function lets you swap line breaks for any other delimiter non-destructively.
- Always check for CHAR(13) as well if your data originated from a Windows text file or external system.
- Turn on wrap text to visually confirm line breaks exist before trying to split them.
Work Smarter with Delimiter Tools
Once you've got your data out of Excel and into a cleaner format, you might need to convert it further. The delimiter converter on this site lets you switch between commas, pipes, tabs, and other separators in seconds. It's a handy step when you're prepping data for import into another system or just cleaning things up.
Understanding how CHAR(10) works makes you a lot more effective with messy spreadsheet data. It's one of those small pieces of knowledge that saves a surprising amount of time.