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?

2

2 Answers

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

1

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

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

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