Pdf to excel formatting (removing unwanted data) | Excel forum

Resolved · Medium Priority · Version 2016

Fri 5 Feb 2021, 11:14 Delegate Marie said...

Marie has attended:
Excel Intermediate course
Excel Intermediate course

Pdf to excel formatting (removing unwanted data)

Hi, I have a second question. I have a list of data on a pdf that is separated into headings ie name; address; tel. When I copy past it to excel it lists the data in one column. Row by row. I only need the name row. How do I get rid of the two extra rows that I don't need, without doing it manually as the list is very long. Thanks.

Fri 5 Feb 2021, 14:04 Edited on Fri 5 Feb 2021, 14:05 Trainer Jens said...

RE: pdf to excel formatting (removing unwanted data)

Hi Marie,

Thank you for the forum question.

You may be able to use the tool Text to Columns you can find on the Data tab, but it depends on your data.

You can use the tool to split the text to several columns by a delimiter and remove unwanted columns. The delimiter could be a space.

If you select all the rows with the data you have pasted from your PDF file and then click Text to Columns you open up a wizard. Just click OK in the first step. On the second step in the wizard you will need to tell Excel the delimiter. Select space and look in the preview if Excel split it as you want. If the name is a first name a space and a last name it will split the Name column into two columns because of the space between the first name and the last name.

On step 3 you can select each column you don't want (address; tel.) and select the option Do not import (skip).

Now just click Finish bottom right.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 5 Feb 2021, 14:21 Trainer Jens said...

RE: pdf to excel formatting (removing unwanted data)

Hi Marie,

I found a video which show you the Text to column tool.

https://www.youtube.com/watch?v=3tCkzCMqopM

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 12 Feb 2021: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Highlighting a data range

Attempting to use a mouse to highlight a large range of cells with data in Excel can make the mouse to have a life of it's own!

Use keyboard strokes instead.

Step 1. Place the cusor in the cell where the highlighting should begin.

Step 2. Select keystroke, CTRL+SHIFT+END

This will take the cursor to the furthermost bottom corner of the data range found in that worksheet. And highlight that range of cells at the same time

View all Excel hints and tips