extract value from cell

[adeel via OfficeKB.com]

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.

 

[T. Valko]

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)

 

[Mike H]

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)

 

[JLatham]

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