To remove line breaks in Excel cells, use Find and Replace: press Ctrl+H, click in the Find box, press Ctrl+J to enter a line break, type a space in Replace, and choose Replace All. For a formula, =SUBSTITUTE(A2, CHAR(10), " ") does the same job and keeps the original text untouched.
Where do line breaks in Excel cells come from after a PDF conversion?
A line break inside a cell comes from text that was printed on two lines in the PDF. When a converter keeps a wrapped description in one cell instead of splitting it into two rows, it often joins the lines with a line feed character, code 10. Excel then shows the cell on two lines whenever Wrap Text is on.
You can see whether a cell holds a break by clicking it and looking at the formula bar, which shows the text on more than one line. A quick count works too: =LEN(A2)-LEN(SUBSTITUTE(A2, CHAR(10), "")) returns how many line feeds the cell holds.
Why the breaks cause trouble
A break is a real character, not a display setting. A search for "CORNER HARDWARE" will not find a cell holding "CORNER" and "HARDWARE" on two lines. A lookup or a COUNTIF on the merchant name misses it. A CSV export can split the row in two when another program reads it.
Turning off Wrap Text hides the break but does not remove it. The cell looks like one line, while the character stays inside. That difference between how a cell looks and what it holds runs through every converted file, and what formatting survives a PDF conversion covers the same gap for numbers and dates.
Find and Replace with Ctrl+J for cell line breaks
Find and Replace removes every line break in a selection in one pass, with no helper column. The trick is that a line break cannot be typed in the Find box, but pressing Ctrl+J there enters one. The box looks empty, or shows a tiny blinking dot, yet it holds the character.
Step list
- Select the column or range to clean. If you select one cell only, Excel searches the whole sheet.
- Press Ctrl+H to open Replace.
- Click in "Find what" and press Ctrl+J once.
- Click in "Replace with" and type one space.
- Choose Replace All, then close the dialog.
If Excel reports that it found nothing, the box may hold two Ctrl+J presses, or the break may be a different character. Clear the Find box with Backspace, press Ctrl+J once more, and try again.
Replacing with a space rather than with nothing is the important choice. The wrapped line usually ended where a space would have been, so deleting the break outright joins the last word of one line to the first word of the next.
SUBSTITUTE with CHAR(10) to turn line breaks into spaces
SUBSTITUTE replaces one piece of text with another inside a cell, and CHAR(10) stands for the line feed. Together, =SUBSTITUTE(A2, CHAR(10), " ") returns the cell's text with each break turned into a space. Use it in a helper column when you want to compare the result with the original before replacing anything.
Some files carry a carriage return, CHAR(13), as well as or instead of the line feed. Nest a second substitution to handle both:
=TRIM(SUBSTITUTE(SUBSTITUTE(A2, CHAR(13), " "), CHAR(10), " "))
The outer TRIM cuts any double spaces the substitutions left behind, along with spaces at the start and end. When the helper column looks right, copy it and paste it over the original with Paste Special, Values.
Four example cells
The cells below are examples. The arrow ↵ marks where a line break sits.
| Cell holds | Method | Result |
|---|---|---|
POS PURCHASE↵CORNER HARDWARE |
SUBSTITUTE with a space |
POS PURCHASE CORNER HARDWARE |
ACH CREDIT ↵ PAYROLL |
SUBSTITUTE inside TRIM |
ACH CREDIT PAYROLL |
WIRE OUT + CHAR(13) + CHAR(10) + REF 5521 |
Nested SUBSTITUTE inside TRIM |
WIRE OUT REF 5521 |
SPRINGFIELD↵IL |
CLEAN alone |
SPRINGFIELDIL |
The last row is the reason not to reach for CLEAN first, as the next section explains.
Does CLEAN remove every line break character?
CLEAN removes line breaks, but it deletes them rather than replacing them, so two words on either side of a break run together. Microsoft's CLEAN function reference says it removes the first 32 nonprinting characters of 7-bit ASCII, codes 0 to 31, which includes both the line feed and the carriage return.
That makes CLEAN useful for stripping odd control characters a PDF sometimes carries, such as a tab or a stray code from a font. It is the wrong first tool for line breaks, because of the joined words in the example table.
Using CLEAN without joining words
Replace the breaks with spaces first, then clean what remains:
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(10), " ")))
The same Microsoft page notes that CLEAN on its own leaves some other nonprinting characters in place. Text that still looks wrong after this formula, for example words with letters missing, usually has a different cause. Repairing missing letters after PDF to Excel covers ligatures and font problems, which no cleanup formula fixes.
Keeping word spaces when line breaks are removed
Word spaces survive when every break becomes a space and TRIM then collapses any doubles. The one case that needs a manual check is a word the PDF hyphenated across two lines, such as PAY- and MENT. Replacing the break with a space gives PAY- MENT, which you fix with a second replace of - with nothing.
Run that second replace with care. A description such as TRANSFER - SAVINGS holds a hyphen followed by a space for a good reason, and a blanket replace would damage it. Filter the column for cells containing - first and look at what you would change.
Longer fields
Brokerage statements are a common source of multi-line cells, because security names and transaction notes run long. Turning a brokerage statement into Excel covers keeping those names together with their quantities and prices.
If a cleaned file then refuses to open, or opens with warnings, the cause is the file rather than the text, and fixing a converted Excel file that is not opening walks through the usual reasons.
FastBankConvert is built to put each transaction's date, description, amount and balance in their own columns. It is getting ready to open; you can ask for a note when trial conversions start at Get started.