Extract Addresses from PDF to Excel, Split into Fields

To extract addresses from a PDF to Excel, first get each address onto one row, then split it into fields. Convert or copy the page so each printed line lands in its own cell, group the lines that belong to one address, and use formulas to cut the last line into city, state and postal code.

How PDF address blocks arrive in Excel

An address block in a PDF is two to four stacked lines: a name, a street, sometimes a suite, then a city line. When that block reaches Excel, each printed line usually becomes its own row, because a PDF stores text as separate runs placed on the page, not as one field called "address".

That stacking is the root of most trouble. A mailing list printed three across, like a sheet of labels, is worse still. The converter reads left to right, so the first line of the first label sits beside the first line of the second label, and three different addresses share a row. Before you split anything, look at the raw result and decide which of these shapes you have:

• One line per row, one address per block. A customer list or a directory printed in a single column. Blank rows usually separate the blocks.
• Labels in a grid. Two or three addresses side by side. Each column of the sheet holds one label column of the page.
• One address per cell. Some tools join the lines of a block with line breaks inside a single cell. The cell looks tall and the text wraps.
• Addresses inside a table. An invoice register or a vendor list with an "Address" column. Here the address may already be on one row but may have wrapped onto a second row below it.

If you are not sure how the converter decided where rows and columns go, how table extraction finds rows and columns in a PDF explains the logic. For the grid shape, split the sheet first: copy each label column into its own block, one under the other, so you are back to a single column of lines.

A cell with line breaks inside it is the easiest shape to fix. =TEXTSPLIT(A2,CHAR(10)) spreads its lines across the row, one line per column, and the rest of this post works the same from there.

How do you join a multi-line address block into one row?

To join a multi-line address block, give every line a block number, then pull the lines of each block side by side. A helper column that counts up by one at each blank row does the numbering, and an INDEX formula reads the first, second and third line of each numbered block into columns.

Number the blocks

Put the raw lines in column A starting at A2, with a blank row between addresses. In B2 enter 1. In B3 enter:

=IF(A2="",B2+1,B2)

Fill it down. Every line of the first address now carries 1, the next address carries 2, and so on. The blank separator rows carry the number of the block above them, which is harmless because you will skip them.

Number the lines inside each block

In C2 enter a line position:

=IF(A2="","",COUNTIFS(B$2:B2,B2,A$2:A2,"<>"))

The first line of a block gets 1, the second gets 2. Blank rows get nothing.

Spread the block across a row

On a new sheet, put the block numbers 1, 2, 3 down column A. In B2, read line 1 of that block:

=IFERROR(INDEX(Raw!$A:$A,MATCH(1,(Raw!$B:$B=$A2)*(Raw!$C:$C=1),0)),"")

Copy it to C2 and D2, changing the final =1 to =2 and =3. Add a fourth column if some addresses have a suite line. Each row now holds one address: name, street, city line.

When there are no blank rows between blocks, look for something else that marks a start. A name line in capitals, a line that starts with a customer number, or a fixed block length of three lines can all drive the counter. With a fixed length of three, =INT((ROW()-2)/3)+1 replaces the blank-row test.

The test that matters is the last line. In a US-style address the city line is the one that ends in a five-digit or nine-digit ZIP code. If any row's last filled column does not end in digits, the grouping went wrong for that block, and you should look at it before splitting.

Splitting an address line into city, state and ZIP code

The city line of a US address has a fixed shape: the city, a comma, a two-letter state code, a space and the ZIP code. That shape lets formulas cut it apart. Take the city from before the comma, the ZIP from after the last space, and the state from between them.

Microsoft's TEXTAFTER reference notes that a negative instance number searches from the end of the text, which is what finds the last space. TEXTAFTER and its partner TEXTBEFORE are in Excel for Microsoft 365 and Excel 2024.

With the city line in D2:

Wrap each one in TRIM, because converted PDF text often carries a trailing space or two spaces where one was printed. TRIM also collapses runs of spaces inside the text.

Keep the ZIP code as text

A ZIP code looks like a number, and Excel will happily turn 02139 into 2139. Postal codes are labels, not quantities. Format the ZIP column as Text before you paste values into it, or keep the formula result, which TEXTAFTER already returns as text.

The street line

The street line usually needs no splitting. If you want the house number in its own column, =TEXTBEFORE(C2," ") takes it, and =TEXTAFTER(C2," ") keeps the rest. Check the result for lines that start with a word, such as "One Market Plaza" or "Rural Route 2", where the first token is not a number.

Five sample addresses split into fields

The worked example below runs five made-up addresses through the steps above. The left column is the raw city line as it came out of the PDF. The other columns are the formula results. None of these addresses is real.

Rows two and three show why TRIM and text formatting matter. The Portland line has double spaces and a trailing space from the PDF, and TRIM absorbs them. The Boston ZIP keeps its leading zero only because the column holds text.

Row four shows that a city name with spaces is fine, because the split uses the comma, not the spaces before it. The nine-digit ZIP also survives as text.

Row five is the useful failure. The PDF printed no comma after the city, so TEXTBEFORE finds nothing and returns an error. An error is better than a wrong answer here. Filter the City column for errors and fix those rows by hand, or add a fallback that treats the last two tokens as state and ZIP:

=IFERROR(TRIM(TEXTBEFORE(D2,",")),TEXTBEFORE(TRIM(D2)," ",-2))

The -2 takes everything before the second-to-last space, which leaves "Austin". Keep the fallback in a separate column at first, so you can compare it with the original before trusting it.

Once the columns are filled, sort by state and ZIP and read down the list. A state value of three letters, a ZIP with four digits, or a city that ends in a comma all point to a row that needs a second look.

Which address formats break a city and state split?

A city and state split breaks on any address that does not end with the city, a comma, a state and a postal code. Suite lines in the wrong place, PO boxes, apartment numbers on the city line, and addresses outside the US are the usual causes. Find them by testing the last line, not by reading every row.

Suite and unit lines

Some lists print "Suite 400" on its own line between the street and the city. With the block method above, that pushes the city line from the third column to the fourth. Pick the city line by position from the end instead: the last non-empty cell of the row. In Excel 365, =TAKE(FILTER(B2:E2,B2:E2<>""),,-1) returns it. Then join the suite back to the street with =TEXTJOIN(", ",TRUE,C2,D2) if you want one street field.

PO boxes and care-of lines

"PO Box 1182" has no house number, and "c/o Finance Dept" adds a line that is neither a name nor a street. Neither breaks the city split, but both can confuse a house-number column. Flag them with =ISNUMBER(SEARCH("box",C2)) and leave the street field whole for those rows.

Addresses outside the US

A Canadian postal code has a space in the middle, so "Toronto, ON M5V 2T6" puts "2T6" in the ZIP column if you split on the last space. A UK address often puts the postcode on its own line and has no state at all. Before you split, sort the list by country, and write a separate set of formulas for each country's format. Do not force every address through the US pattern.

Names that wrap

A long company name can wrap onto a second line in the PDF, which turns a three-line block into four. The ZIP test catches it: if the last line ends in digits, the city line is correct whatever happened above it. Join the two name lines with TEXTJOIN and move on.

Address lists too long for worksheet formulas

Worksheet formulas are fine for a few hundred addresses. When the list runs to many thousands, or when you get a new PDF of the same layout every month, the grouping and splitting are better done once in a script and saved as a CSV that Excel opens ready to use.

A script does the same three steps: read the page text line by line, group lines into address blocks, and split the last line. The difference is that it runs again on next month's file without any copying. If your team works in JavaScript, extracting a PDF table in JavaScript shows how to read the page text in Node. If you use R, reading a PDF table into an R data frame covers the same ground with a tidy data frame at the end.

A script can also apply rules that are awkward in a cell, such as a list of two-letter state codes to validate against, or a separate pattern for each country. Rows that fail validation go to a review file instead of the main output.

The output of a large job is often a CSV with tens of thousands of rows. Opening it by double-clicking can strip leading zeros from ZIP codes and change other columns. Opening a large CSV in Excel without losing data walks through importing it with each column's type set first, so the postal codes stay as text.

Addresses on a bank statement are a smaller case: the account holder's mailing address in the header. FastBankConvert is aimed at the transaction lines, turning dates, descriptions, amounts and balances into columns. It is not open yet, and the get-started page takes your email for the launch.