Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

wec43wec

Technical User
Aug 6, 2005
226
US
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.

 
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
 
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
 
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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top