Excel 2010 - How to extract Specific Data from a String of Text 1

[wec43wec]

Sample Data =

Zip Code Address Directional Donation / Land Income
123456789 123 Main Street South 100 / Bus. 10,000
987654321 12345 Oak Bar Ave Northside 10000 / Resd. 100,000

I have a PDF file of over 10,000 rows with a string of data as listed in the sample above which I need to separate into columns (not using VBA) within Excel 2010. The only common factor is a fixed number for the zip code and a "forward slash" in the middle of the text string which is immediately after the number for donation.

1. 1 used the "left" function to extract the zip code.

2. I need to extract the donation number which is always to the left of the "forward slash" by 3 to 6 digits.

3. I need to also extract:
the "address" which has no fixed length and is after the zip code, but before the donations
the "dirrectional" only (which could be - West / East / South / North / Northwest/ etc)
the "land" which is immediately AFTER the forward slash, which could be 3 to 7 characters.

Note: I tried to use the "delimter" function, but because the data under each column was of different lengths, it did no work.

The main data I need to extract is the data mentioned above that is before the "forward slash".

Please share with me any ideas to resolve this issue and thank you
for your help.

 

[Andrzejek]

Is your data as you showed:
Zip Code Address Directional Donation / Land Income
123456789 123 Main Street South 100 / Bus. 10,000
987654321 12345 Oak Bar Ave Northside 10000 / Resd. 100,000

or is it this way (aligned, fixed length records):[pre]
Zip Code Address Directional Donation / Land Income
123456789 123 Main Street South 100 / Bus. 10,000
987654321 12345 Oak Bar Ave Northside 10000 / Resd. 100,000
[/pre]


Have fun.

---- Andy

 

[wec43wec]

Andy the string of text is not aligned by column heading which is why I need to find a why to pull the text with the only common factors being the forward slash and the zip code.

Please help !

Thanks

 

[SkipVought]

hi,

Use Data > Text to columns to parse in two steps:

step 1 - FIXED on column 9 results in 9 character zip in column A and the remainder in column B
step 2 - parse column B DELIMITED on / reaults in address & donation in column B and remainder in column C

Use these formulas to return the address & donation
[tt]
D2: =LEFT($B2,FIND(" ",$B2,LEN($B2)-7)-1)
E2: =RIGHT($B2,LEN($B2)-FIND(" ",$B2,LEN($B2)-7))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wec43wec]

Skip - your timely and accurate response to my issues allow me and others to have a peaceful day.

I truly appreciate your help !

Multiple STARS are coming your way.

Again, thanks

WEC43WEC