When a PDF copy gives no spaces, the file usually never stored space characters at all. It placed each word at an exact position and left the gap as empty page. Your viewer must guess where words break, and some guess badly. Try another viewer, a converter that reads positions, or the page's HTML version before pasting into Excel.
Why PDF copy gives no spaces between words
PDF copy gives no spaces because many PDFs draw words by position rather than by typing a space between them. The program that made the file moved to the next word's location and drew it there. On screen the gap looks like a space, but the text stream holds "Openingbalance" with no space character in it.
A PDF page is a set of drawing instructions. Each one says, in effect, "draw these characters starting here". Some programs write a real space character between words. Others skip it, because jumping to the next position gives the same visual result and keeps the file smaller.
The PDF 32000-1:2008 specification addresses this for one kind of file. In section 14.8.2.5, it says that in a Tagged PDF, a file that carries structure for accessibility, "the spacing characters that would be present to separate words in a pure text representation shall be present". A note adds that a reader of such a file "does not need to guess about word breaks based on information such as glyph positioning". The flip side is that untagged files make no such promise, and readers do have to guess.
Does every PDF viewer drop the same spaces?
Viewers do not all drop the same spaces, because each one guesses word breaks in its own way. One app may add a space whenever the gap between two characters is wider than usual, while another adds one only where a space character exists. Copying the same line in two viewers often gives two different results.
Here is an example of one statement line copied three ways. The text is made up.
| Source of the copy | What pastes |
|---|---|
| Viewer A, uses only stored spaces | 03/14CARDPURCHASEGREENLEAFMARKET-42.181,206.30 |
| Viewer B, adds spaces at wide gaps | 03/14 CARD PURCHASE GREENLEAF MARKET -42.18 1,206.30 |
| A converter that reads positions into columns | 03/14 · CARD PURCHASE GREENLEAF MARKET · -42.18 · 1,206.30 |
Viewer B gets the words right, but the result is still one line of text that you would need to split. The converter skips the guessing problem for the columns, because it uses the positions to decide which cell each piece belongs in.
Copying can also go wrong in the other direction. A viewer that guesses aggressively may break one word in two, giving "GREEN LEAF". And on some pages the words come out spaced but in the wrong order, which is a separate fault; why PDF reading order comes out scrambled explains it.
Getting spaced PDF text back before Excel
The best fix is to get spaced text before it reaches Excel, because Excel cannot tell where one word ends and the next begins. Try routes in this order: a second viewer, a different source document, then a tool that reads text positions into columns.
- Copy in a second viewer. A browser's built-in PDF viewer and a desktop reader often handle spacing differently. Paste into a plain text editor to compare.
- Find another version of the same document. Many banks also show the statement or transaction list as a web page, and HTML stores real spaces. Converting an HTML bank statement to Excel covers statements sent as web pages or emails.
- Use a converter that places text by position. It does not need space characters, because it measures the gaps itself.
- Run OCR on the page. As a last resort, OCR reads the page as an image and inserts spaces where it sees gaps. It may misread digits, so check the totals.
On a tablet, the same choices apply, with one advantage: a larger screen makes it easier to check the result against the page. Converting a PDF to Excel on an iPad shows how to upload from Files and compare rows side by side.
Can Excel put PDF spaces back after pasting?
Excel can only partly put spaces back after pasting, and only where the text follows a pattern. It has no dictionary, so it cannot split "CARDPURCHASE" into two words. It can split at a change from letters to digits, or at a decimal point followed by two digits, which helps with amounts.
What works:
- Flash Fill on the Data tab. Type the spaced version of the first two cells by hand, then press Ctrl+E. Excel copies the pattern down the column. It works when every row has the same shape, and fails quietly when it does not, so check a sample.
- Splitting by pattern. If a cell always starts with a five-character date such as 03/14,
=LEFT(A2,5)takes the date and=MID(A2,6,200)takes the rest. - Fixing words by hand. For a short list of merchant names, find and replace "CARDPURCHASE" with "CARD PURCHASE" and so on.
For more than a page or two, these are slower than starting again from a better source.
PDF copy no spaces in numbers and dates
Missing spaces between numbers and dates are worse than missing spaces between words, because the joined value is still a valid-looking number. "-42.181,206.30" mixes an amount and a balance, and a careless split can give -42.1 and 81,206.30. Fix numbers from a better source, not by guesswork.
Each figure on a bank statement usually has two decimal places, which gives you one safe rule: an amount ends two digits after its decimal point. In "-42.181,206.30", the first amount is "-42.18" and what follows, "1,206.30", is the next figure. Use that rule to check any split you make, then confirm with the running balance: the previous balance plus the amount should equal the balance on the line.
Dates joined to descriptions are easier, because a date has a fixed length. Joined dates and amounts, such as "03/1442.18", are risky: 03/14 then 42.18, or 03/1 then 442.18? Check the statement.
FastBankConvert is getting ready to open as a converter that puts date, description, amount and balance in their own columns and warns you before download if the balances do not add up. Get started to join.