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.aspx/ms-excel/201001/1

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.aspx/ms-excel/201001/1
> 

The question doesn't make sense or at least not to me.

> 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)

You already have the street names in column C
-- 
Mike

When competing hypotheses are equal, adopt the hypothesis that introduces 
the fewest assumptions while still sufficiently answering the question. 
Occam''s razor (Abbrev)


"adeel via OfficeKB.com" wrote:

> 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.aspx/ms-excel/201001/1
> 
> .
> 

It would be nice if there was some unique character at the end of the street 
portion of your addresses in column A.  We could do it with a built in 
worksheet function easily then.  For example, if the street portion always 
ended with a comma (,) then this formula (for the entry in A1) would do it:
=LEFT(A1,FIND(",",A1)-1)

But I'm making an assumption that it's not that easy.  I'm also assuming 
that the street address is the very first part of the entries in column A, as 
you stated.  We can build a User Defined Function (UDF) to deal with this.

Copy the code below and put it into a regular code module in your workbook.  
To do that, open the workbook, press [Alt]+[F11] to enter the Visual Basic 
(VB) Editor and use Insert --> Module in it to get a new code module.  Copy 
the code and paste it into that module, close the VB Editor.
Now in cell B1, put this formula:
=GetStreetInformation(A1)
fill that formula down to A100 and you should see your addresses in column B.

NOTE:  If you have entries in column C with similar street names, such as 
Main Street
North Main Street
S. Main Street
then put them in order so that the common part is in the list after others 
with similar names, as
North Main Street
S. Main Street
Main Street
otherwise the code will always find "Main Street" before even looking at 
North Main Street or S. Main Street

OK, here's the code

Function GetStreetInformation(FullAddress As Range) As String
  Dim streetList As Range
  Dim anyStreet As Range
  Dim testAddress As String
  
  testAddress = FullAddress.Value
  Set streetList = ActiveSheet.Range("C1:" & _
   ActiveSheet.Range("C" & Rows.Count).End(xlUp).Address)
  GetStreetInformation = "Street Not Listed"
  For Each anyStreet In streetList
    If InStr(testAddress, anyStreet) > 0 Then
      GetStreetInformation = Left(testAddress, _
       InStr(testAddress, anyStreet) + Len(anyStreet))
      Exit For
    End If
  Next
End Function





"adeel via OfficeKB.com" wrote:

> 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.aspx/ms-excel/201001/1
> 
> .
>