Join Wrapped Rows in Excel After a PDF Conversion

To join wrapped rows in Excel, first flag every row that has description text but no date and no amount. Then use a helper column to append each flagged row's text to the transaction row above it, paste the result as values, and filter out the flagged rows. The whole repair takes three formulas and one filter.

Why do wrapped rows appear after a PDF conversion?

A wrapped row appears when a bank prints a long description over two lines in one table cell, and the converter treats each printed line as a separate row. The first row carries the date, the first part of the description and the amount. The second row carries only the rest of the description, with the other columns empty.

A PDF has no idea that two lines of text belong to one cell. It stores each line as a run of characters at a position on the page. A converter has to guess whether a line that starts lower down is a new transaction or the second half of the last one, and some guess wrong.

What a wrapped row looks like

The signs are consistent across most statements:

• The row has text in the description column only.
• The text reads like the end of a sentence, such as a city name, a reference number or the tail of a merchant name.
• The row above it has a date and an amount, and its description stops mid-word or mid-phrase.
• A long description can wrap more than once, leaving two or three orphan rows under one transaction.

Wrapped rows break more than the look of the sheet. A count of rows overstates the number of transactions, a filter on the description misses the half that sits on another row, and a lookup on the merchant name finds only part of it.

Flagging the orphan rows in a wrapped statement

An orphan row is a continuation line: it has description text but no date, no debit and no credit. Flag each one in a helper column with a formula that returns TRUE when all three of those cells are empty. The flag drives every later step, so check it before you join anything.

Assume an example layout: date in column A, description in B, debit in C, credit in D. In a new column G, enter this in G2 and fill it down:

=AND(B2<>"", A2="", C2="", D2="")

The formula returns TRUE for a row with text and nothing else, and FALSE for a transaction row or a fully empty row.

When the date test misleads

Some statements print the date only once per day and leave it blank on the next rows of the same day. On those statements, a missing date does not mean a continuation line. Drop the date test and flag on the amounts alone:

=AND(B2<>"", C2="", D2="")

Or fill the dates down first, as filling down blank dates for grouped rows describes, and then use the full test.

Cells that look empty but are not

An amount cell can look empty while holding a space, or a lone currency symbol left behind when the number moved elsewhere. Those cells make the flag return FALSE. Test a suspect cell with =LEN(C2); anything above zero means it holds a character. If you are about to strip symbols from the whole column anyway, removing currency symbols in Excel does both jobs at once.

Joining wrapped description text upward with a helper column

A helper column can collect each transaction's full description by reading from the bottom of the sheet up. Each row takes its own text, and adds the next row's collected text only when the next row is an orphan. The transaction row then holds the whole description, however many lines it wrapped over.

Put this in H2 and fill it down to the last row:

=B2 & IF(G3, " " & H3, "")

The formula refers to the row below, so a three-line description chains together: the second orphan's text flows into the first orphan's H cell, and that flows into the transaction row. Orphan rows end up holding partial text in column H, which does not matter because they will be deleted.

Paste the joined text as values

Formulas in column H depend on the rows below them. Delete those rows and the formulas return #REF!. Before deleting anything, select column H, copy it, and use Paste Special, Values on the same cells. Then copy H over B, or keep H and delete B.

The space between the pieces

The " " in the formula puts one space between the two halves. If the bank wrapped mid-word, as in AMAZO on one line and N MKTP on the next, the join gives AMAZO N MKTP. That is rare, and easier to fix by hand than to predict with a formula. If the pieces already end with a space, =TRIM(H2) tidies the doubled spaces afterwards.

Joining wrapped rows with TEXTJOIN and a group number

In Excel 365, a group number column gives every row of one transaction the same number, and TEXTJOIN with FILTER joins all the rows of a group in one formula. This route reads more plainly than the bottom-up chain and, once the group numbers are pasted as values, does not depend on row order.

Building the group number

In I1, enter 0. In I2, enter this and fill down:

=IF(G2, I1, I1+1)

A transaction row starts a new number. An orphan row keeps the number of the row above. Every transaction and its wrapped lines now share a value in column I.

Joining by group

In J2, enter:

=IF(G2, "", TEXTJOIN(" ", TRUE, FILTER($B$2:$B$500, $I$2:$I$500=I2)))

Adjust the range to the length of your sheet. The formula leaves orphan rows blank and gives each transaction row its complete description. TEXTJOIN skips empty cells because of its second argument, TRUE.

FILTER and TEXTJOIN are not in older versions such as Excel 2016. If either returns #NAME?, use the helper column route in the previous section instead.

Deleting the spare wrapped rows safely

Delete the spare rows by filtering the flag column for TRUE, selecting the visible rows, and deleting them in one action. Do this only after the joined text has been pasted as values, and only after you have checked that every flagged row really is a continuation line.

Step list for the delete

1. Paste the joined column as values, as described above.
2. Select the header row and turn on Data, Filter.
3. Filter column G to show only TRUE.
4. Select the visible data rows by their row numbers, right-click, and choose Delete Row.
5. Clear the filter and check that every remaining row has an amount.

Excel deletes only the visible rows when a filter is on, so the transaction rows stay. If you prefer not to delete, sort by the flag column instead, which groups the orphans at the bottom, but sort only after the joined text is values.

Count before and after

Write down the number of rows before the delete and the number of TRUE flags. After the delete, the row count should be the first number minus the second. Then check that the debit and credit totals have not changed, since an orphan row should never hold money.

When is a wrapped-looking row a real transaction?

A row with no amount is not always a continuation line. Some statements print a memo line, a total label or a page heading with text and no amount, and some print a fee or interest line whose amount sits in the wrong column after conversion. Those rows must be moved or fixed, not joined into the row above.

Rows to look at before joining

• Amount in the wrong column. If a row's amount slid into the description or balance column, the flag sees no amount and marks it as an orphan. Lining up columns shifted after PDF to Excel covers how to put the value back first.
• Amounts stored as text. An amount stored as text still counts as not empty, so it does not cause a false flag. It will cause trouble when you total the column, and making converted numbers editable fixes that.
• Section labels. A line such as "Deposits and other credits" has text and no amount. Joining it would paste a heading into the end of a transaction's description.
• Balance forward lines. "Balance brought forward" usually has a balance but no date, debit or credit. It is not an orphan, but the flag above does not look at the balance column, so it marks the row TRUE. If your sheet has a balance in column E, add E2="" to the flag formula.

Read down the flagged rows once before you join. A label that reads like a heading rather than the end of a merchant name is the giveaway.

Scanned statements

On a scanned or photographed statement, the reading step itself can split a line, and orphan rows can also carry stray characters read from a stamp or a fold. The same flag works, but expect more manual checks. If the source is a printed grid rather than a statement, scanning a printed spreadsheet back into cells covers keeping its rows square from the start.

Wrapped rows worked example: seven rows become four transactions

The example below uses made-up values in the A to D layout described earlier. Seven converted rows hold four transactions, two of which wrapped. Column G is the orphan flag and column H is the joined text after the bottom-up formula.

Row 4 has no debit because it is a credit, so its flag reads the credit column, not shown here, and returns FALSE. Row 5 wrapped twice, and the chain in column H carried both orphan lines up.

The result

After pasting H as values and deleting rows 3, 6 and 7, four rows remain: one per transaction, each with its full description. The debit total is 260.10 before and after, which confirms that no money sat on an orphan row.

A converter that keeps wrapped text with its transaction saves this whole repair. FastBankConvert is being built to put every transaction in its own row with its date, description, amount and balance; it has not opened yet, and the FastBankConvert home page shows the plan.