# Remove Line Breaks From Copied PDF Text in Excel

- Author: CheapBankConvert (https://cheapbankconvert.com)
- Published: 2026-10-17
- Category: The cost of manual data entry
- Canonical: https://cheapbankconvert.com/blog/remove-line-breaks-from-copied-pdf-text

> Remove line breaks from copied PDF text so each transaction sits on one row: CLEAN, SUBSTITUTE and Find and Replace, with a row-count check.
To remove line breaks from copied PDF text in Excel, wrap the cell in `=TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(13)," "),CHAR(10)," "))`, or use Find and Replace with Ctrl+J in the Find box. That fixes breaks inside a cell. When one transaction lands on two rows, join the rows instead, then compare the row count with the statement.

## Why does copied PDF text carry line breaks into Excel?

A PDF stores text as positioned lines, not as paragraphs or table cells. When a bank prints a long payee name over two lines, the PDF holds two separate lines, and copying keeps the break between them. Excel then receives that break either inside one cell or as the start of a new row.

Which of the two you get depends on how you paste. Pasting into the formula bar or into a cell in edit mode keeps the text in one cell, with a hidden line break character in the middle. Pasting onto a selected cell normally splits at every break, so each printed line takes its own row.

Two hidden characters do the breaking. Character 10 is a line feed, and character 13 is a carriage return. Some copied text carries both, as a pair: 13 then 10. A fix that removes only one leaves the other behind, which is why the formulas below handle both.

If you only need one column of a statement, such as the amounts, selecting just that column avoids most breaks in the first place. [Selecting a column in a PDF](/blog/select-a-column-in-a-pdf) shows the readers that support a column selection.

## Removing PDF line breaks inside cells with CLEAN and SUBSTITUTE

CLEAN deletes non-printing characters, including line breaks, in one step. SUBSTITUTE swaps each break for a space, which keeps the words on either side apart. For payee names, SUBSTITUTE wrapped in TRIM is the safer choice, because CLEAN can glue the last word of one line to the first word of the next.

Microsoft's [CLEAN function reference](https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41) says the function removes the first 32 non-printing characters in 7-bit ASCII, values 0 to 31. Both line break characters fall in that range. The same page notes that CLEAN does not remove some other non-printing characters found in Unicode text, such as value 127.

Put the formula in a helper column next to the pasted text. The cell contents below are examples; the ¶ mark stands for a hidden line break.

| Formula | Example input in A2 | Result |
| --- | --- | --- |
| `=CLEAN(A2)` | CITY ELECTRIC¶CO PAYMENT | CITY ELECTRICCO PAYMENT |
| `=SUBSTITUTE(A2,CHAR(10)," ")` | CITY ELECTRIC¶CO PAYMENT | CITY ELECTRIC CO PAYMENT |
| `=TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(13)," "),CHAR(10)," "))` | CITY ELECTRIC¶CO PAYMENT | CITY ELECTRIC CO PAYMENT, with no double spaces |

The third formula handles both break characters and then TRIM squeezes any doubled spaces to one. Copy it down, then copy the helper column and paste it back over the original as values, and delete the helper.

## Find and Replace with Ctrl+J for copied PDF line breaks

Find and Replace can remove line breaks across a whole column without a formula. Select the column, press Ctrl+H, click in the Find what box and press Ctrl+J, type a space in Replace with, and click Replace All. Ctrl+J enters the line feed character, which looks like nothing in the box.

A few things trip people up with this method:

- **The Find box looks empty.** After Ctrl+J you may see only a tiny blinking dot or nothing at all. It is still there. If Replace All reports zero replacements, the box may hold two Ctrl+J characters from a previous attempt; clear it with Backspace a couple of times and try again.
- **Carriage returns stay.** Ctrl+J finds character 10 only. If the cells still look broken afterward, use the SUBSTITUTE formula for character 13.
- **Double spaces appear.** Replacing a break with a space next to an existing space leaves two. Run TRIM over the column afterward, or replace two spaces with one until Excel finds no more.

Select only the column you are fixing before you start. Replace All on the whole sheet also changes cells you meant to keep, such as notes with deliberate breaks.

## What if one PDF transaction lands on two Excel rows?

When a wrapped payee splits into two rows, the second row holds only the end of the description, with no date and no amount. Joining it back means appending its text to the row above and then deleting it. A helper formula does this for every row at once, so you only delete the leftovers.

Assume dates are in column A, descriptions in column B and amounts in column C, starting at row 2. In D2, put:

`=IF(AND(A3="",C3=""),B2&" "&B3,B2)`

Copy it down. For a normal row, D shows the description unchanged. For a row whose next row has no date and no amount, D joins the two pieces. Then:

1. Copy column D and paste it over column B as values.
2. Filter column A for blanks and delete the visible rows, which are the leftover second lines.
3. Clear the filter and delete column D.

This handles a description wrapped over two lines. One that runs to three needs the step run twice. The broader cleanup of a pasted statement, including splitting it into columns, is in [how to copy a PDF into Excel and fix the paste](/blog/how-to-copy-a-pdf-into-excel).

## Row-count check after removing PDF line breaks

A row-count check proves the cleanup neither lost a transaction nor merged two. Count the transactions printed on one statement page, count your rows for that page, and compare. If they match on a sample page and the totals tie, the line break fixes worked.

The check takes a few minutes:

1. Pick one page of the PDF and count the printed transactions, ignoring headers and subtotals.
2. In your sheet, select the rows for that page and read the count Excel shows in the status bar.
3. If your count is higher, a wrapped line survived as its own row.
4. If it is lower, two transactions were joined. Look for a description that contains two payee names, or an amount next to a row that should have had its own.
5. Finally, compare the sum of your amount column with the statement's totals.

Each round of paste, fix and check is time you would not spend with a clean file. Moving the same numbers through several hands and formats has a cost of its own, which [rekeying data](/blog/rekeying-data) puts a price on. A fresh download from the bank is often the cleanest starting point, and [downloading PNC statements](/blog/download-pnc-statements) is one bank-specific walkthrough. For a text-based PDF, CheapBankConvert plans to output one row per transaction in CSV or Excel; it is not open yet, and the [homepage](/) has the early prices.