I have a dilemma I am hoping someone might be able to help me with! I have exported a PDF file into excel that has a lot of information that populates into one cell. I need to split this information into separate cells, but all the information has different spacing. Here is an example:

John Smith 1234 Main St
Charlie Motto 6548 Teflon Ave
Shannon Guesswho 12W546 Spoon Dr

So, I need to put the names in one cell and the addresses (Preferably numeric and street separate).

Is there any way to do this?

  • Copy/paste? My experience of PDF extractions is that they are often messy and there is no easy way to deal with them – cybernetic.nomad Oct 10 '18 at 21:39
  • How do you want to split the name and address? – Lee Oct 11 '18 at 9:33

Try this:

  1. Copy from PDF
  2. Paste in text file (I use Notepad)
  3. Copy from text file
  4. Paste in excel
  5. Select "Use Text Import Wizard…" in the paste icon
  6. Select Next 3 times and see if the results are correct

You can manage the wizard to request best results

  • I don’t see ‘‘Use Text Import Wizard…’’ in Excel 2013. – Scott Oct 11 '18 at 5:25

In Excel 2013:

  1. Copy text (from PDF file).
  2. Click in the spreadsheet in the upper left corner of where you want the data to go.
  3. Paste.  You may want to do “Paste”  → ”Match Destination Formatting (M)”.

    Ensure that the left column (Column A) is selected. 

  4. Select “Data”  → “Data Tools”  → “Text to Columns”.

  5. “Step 1 of 3” – Select “Delimited” (because your strings are delimited by spaces, and do not have a fixed width).  Click Next >.

  6. “Step 2 of 3” – Select the appropriate delimiter.  In your case, it looks like it is space.  Click Next >.

  7. “Step 3 of 3” – Optionally, assign a data format to each column.  I usually skip this step.  Click Finish.  You’ll get something like this:

  8. If you want street name and street type together,

    • Enter =D1 & " " & E1 into cell F1.
    • Drag/fill down:

    • Select Column F.
    • Copy it, and paste values over Column D.
    • Clear Columns E and F.
  9. Combine Column A (first name) and Column B (last name) similarly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.