Selecting specific cells

**arambo** · 07-07-2010, 10:13 AM

I am importing data and it is being copied from a .pdf sometime I get extra rows that I don't want. out of the list below I only want to select the cells with 8 numbers in it that start with 7. how would I do that? like I said these are coming from a pdf I would really like to convert them over straight from pdf to excel. I tried zamzar with no luck. I there a better way to convert it.
i would appreciate any help this has been driving me nuts. love the site by the way it has helped a lot

1 73641949 $15.00 966203 20100615 20100217 400046 563785
1 73641370 $15.00 966204 20100615 20100217 400393 556728
1 73631865 $15.00 966213 20100615 20100217 400999 556795
1 73814023 $15.00 966214 20100615 20100218 400554 555260
1 73747681 $15.00 966218 20100615 20100218 400950 555225
1 73751673 $15.00 966222 20100615 20100219 400156 558077
1 73751436 $15.00 966224 20100615 20100219 400310 566679
1 73808767 $15.00 966225 20100615 20100219 400328 558094
73644768 $15.00 966207 20100615 20100217 400566 556734
6/5/2010 3 2 73821222 $15.00 966209 20100615 20100217 400706 557186
6/5/2010 3 2 73823953 $15.00 966241 20100615 20100222 400115 566777
6/5/2010 3 2 73827486 $15.00 966250 20100615 20100223 400247 557210
6/5/2010 3 2 73829790 $15.00 966258 20100615 20100223 400840 572036
6/5/2010 3 2 73827988 $15.00 966267 20100615 20100224 400960 558682
6/11/2010 3 2 73828002 $15.00 966270 20100615 20100225 400003 558351
6/5/2010 3 2 73827985 $15.00 966274 20100615 20100225 400659 571076
6/11/2010 3 3 73771120 $15.00 966161 20100615 20100127 400029 549143
6/11/2010 3 3 73853927 $15.00 966206 20100615 20100217 400504 550471

**ChemistB** · 07-07-2010, 10:33 AM

It can probably be done nicely with VBA but here's a simple way to do it with formulas.

1. Import data and do Text to columns (if necessary) to separate values into cells

2. Select data and name it "Table"

3. Go to separate sheet and in A1,
=IF(AND(LEN(Table)=8,LEFT(Table,1)="7"),Table,"")

4. Drag that down and across to cover entire Table length (#VALUE will show up once outside the defined range). Your numbers will show up.

5. Copy and Paste Special >Values to remove formulas. Then you can collect the numbers into a single row or column.

See example (I didn't do step 5)

**Mordred** · 07-07-2010, 10:58 AM

There is also a program called "Able2Extract" that lets users decide on specific data to extract from a PDF. It works alright but the paste to cells function in it is not friendly. If you don't want to spend the money then follow ChemistB's advice.

**arambo** · 07-07-2010, 12:32 PM

that helped out a lot. it looks like you pasted that formula in every cell is that correct? how about if i wanted to keep the corresponding dollar amount in there as well? i'm confused on what you named table and what a VBA is.

**ChemistB** · 07-07-2010, 12:58 PM

it looks like you pasted that formula in every cell is that correct?

You can copy and paste or you can drag (grab the lower right corner of the cell with the formula and drag down or across. The formula will automatically adjust to each new cell.

what a VBA is.

VBA is Visual Basic for Applications and is the computer coding used to record macros. I did not use VBA so not to worry about that.

i'm confused on what you named table

I named the original data "Table" In order to do this, simply select all your data and then go to the name box (small white box in toolbar where you normally see the cell name (i.e A2)) and type Table and hit Enter. If you go to that box in the example I gave, there will be a dropdown where you can select Table and see what Table represents.

Actually, I think I made it more complicated than it needs to be. It is probably better to directly refer to the cells rather than use Table. The formula in A1 of Results would be

Please Login or Register  to view this content.

Then you don't need to worry about naming ranges or #Values, etc.

how about if i wanted to keep the corresponding dollar amount in there as well?

Then we need a more complex formula....
Starting in B1, dragged down and across (leave col A as is)
=IF(OR(LEN(A1)>7,AND(LEN(Data!B1)=8,LEFT(Data!B1,1)="7")),Data!B1,"")
That looks to the column to the left to see if it contains an 8 digit number then it pulls that number. You can't use this in Column A because there is no data to the left. You'll need to reformat as $.
Hope that helps.

**arambo** · 07-07-2010, 01:03 PM

I havn't tried it yet because i'm swamped with work but i'm going to fuss with it tomorrow. Thank you so much i'm sure it will work perfectly.

I'll mark it as solved after i test it. Thanks again.