One way...

Assume these are the addresses in the range A2:A8 -

111 2nd St
2 Golfview Dr
33 Long St
1455 Cherry St
45 Thorn Run Rd
299 Dead End Dr
100 Smithfield St

Assume these are the street names in the range H2:H5 -

Cherry
Golfview
Dead End
2nd

Enter this formula in B2 and copy down as needed:

=LOOKUP(1E100,SEARCH(H$2:H$5,A2),H$2:H$5)

--
Biff
Microsoft Excel MVP


"adeel via OfficeKB.com" <u32736@uwe wrote in message
news:a21c3d38c3f9f@uwe...
I have following data:

1) In column A1 to A100 there are various addressess (which includes
house
no., street name etc.)
2) In column C1 to C50 there are only Street Names.

Now, i want a function that extract Street Name from address column (A1 to
A100) and show in front of each cell. Street Names are to be match from
Street Names column (C1 to C50).

hope some one understand..thanks.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/201001/1