How can I parse time and address from cell with non standard extra text?
 

Ok here is my goal.
On Thursday my local newspaper post Garage sell ads for the up coming
weekend.
I've found these sales are an excellent source for merchandise to sell
on ebay. And the prices are awesome.

If I open the paper site in Excel I get cells that look like this. (50
- 100 ads)

How can parse out just the time and address of the sale so I can plan
my routes and which days to visit which house.
(Folks mark the stuff down on the lastday)


1. Come see at: 4785 SE 133rd Dr, City, State 12345 Off Holgate, take
a right on 134th, (Aspen Meadows), stop sign take a right, take a left
on 133rd and 5th house on the left. Saturday August 11, 2007 10am to
5pm only

2. Lots of Name Brands!! Tons of Clothes for Girls and Boys. Dog
House, Animal Kennel, toys, lego table, infant chairs, girl HOPE TO
SEE YOU THERE, THANK YOU!!!! This Friday & Saturday!! 8/10 & 8/11 10am
- 6pm 2100 SE 118th Ave City, St 12345

3. Lots of Name Brands!! Tons of Clothes for Girls and Boys. Dog
House, Animal Kennel, toys, lego table, infant chairs, girl HOPE TO
SEE YOU THERE, THANK YOU!!!! Fri & Sat Aug 10 & Aug 11 10 am - 6 pm
2100 SE 120th Ave City, St no zip

Steve

How can I parse time and address from cell with non standard extra
 

There is no way to programmatically parse inconsistently structured data.
With data that is consistently formatted you can use something like:
=IF(AF9 <
"",(MID(AF9,SEARCH("BNF:",AF9),SEARCH("ID:",AF 9)-SEARCH("BNF:",AF9))),"")

which strips out anything between "BNF" and "ID" in the text in AF9

But here all the rows are consistent in that they have "BNF and "ID" in the
text string, "ID" ALWAYS follows "BNF", and the info I want is ALWAYS
between the two. In the case of your newspaper adds that is not the case.

"Steve" wrote:



Ok here is my goal.
On Thursday my local newspaper post Garage sell ads for the up coming
weekend.
I've found these sales are an excellent source for merchandise to sell
on ebay. And the prices are awesome.

If I open the paper site in Excel I get cells that look like this. (50
- 100 ads)

How can parse out just the time and address of the sale so I can plan
my routes and which days to visit which house.
(Folks mark the stuff down on the lastday)


1. Come see at: 4785 SE 133rd Dr, City, State 12345 Off Holgate, take
a right on 134th, (Aspen Meadows), stop sign take a right, take a left
on 133rd and 5th house on the left. Saturday August 11, 2007 10am to
5pm only

2. Lots of Name Brands!! Tons of Clothes for Girls and Boys. Dog
House, Animal Kennel, toys, lego table, infant chairs, girl HOPE TO
SEE YOU THERE, THANK YOU!!!! This Friday & Saturday!! 8/10 & 8/11 10am
- 6pm 2100 SE 118th Ave City, St 12345

3. Lots of Name Brands!! Tons of Clothes for Girls and Boys. Dog
House, Animal Kennel, toys, lego table, infant chairs, girl HOPE TO
SEE YOU THERE, THANK YOU!!!! Fri & Sat Aug 10 & Aug 11 10 am - 6 pm
2100 SE 120th Ave City, St no zip

Steve