Using SUBSTITUTE multiple times on single cell of data
 

I hope I worded that correctly!

I have a sheet with street addresses. Example:

A B C
123 Elm Street 90210
456 Main Ave 90210

I am trying to create a formula where I can create links to Whitepages.com
to look up phone numbers. The final result I'd like is a link that looks like
this:

http://www.whitepages.com/9900/searc...0210&state_id=

So far I know that I can do this:

=SUBSTITUTE("http://www.whitepages.com/9900/search/ReverseAddress?housenumber=123&street=Elm+St&city_ zip=90210&state_id=",123,A1)

But how do I write a formula that will give me the full result with not only
the House Number, but the Street Name and Zip code?

I guess my main question is how to run the formula for multiple results? Am
I even going about this the right way?

Thanks for any help!!

Using SUBSTITUTE multiple times on single cell of data
 

I put this in A1--just to make it a little easier to read the formula:

http://www.whitepages.com/9900/searc...s?housenumber=

Then with my data in A2:Cxx, I used this in D2:Dxx:

=HYPERLINK($A$1&A2&"&street="&SUBSTITUTE(B2," ","%20")
&"&city_zip="&TEXT(C2,"00000"))

(all one cell)

The %20 is code for a space in HTML lingo.

It didn't find a house, though.

If you want to embed that first part of the link:


=HYPERLINK("http://www.whitepages.com/9900/search/ReverseAddress?housenumber="
&A2&"&street="&SUBSTITUTE(B2," ","%20")
&"&city_zip="&TEXT(C2,"00000"))

(Still all one cell)



Cameron wrote:

I hope I worded that correctly!

I have a sheet with street addresses. Example:

A B C
123 Elm Street 90210
456 Main Ave 90210

I am trying to create a formula where I can create links to Whitepages.com
to look up phone numbers. The final result I'd like is a link that looks like
this:

http://www.whitepages.com/9900/searc...0210&state_id=

So far I know that I can do this:

=SUBSTITUTE("http://www.whitepages.com/9900/search/ReverseAddress?housenumber=123&street=Elm+St&city_ zip=90210&state_id=",123,A1)

But how do I write a formula that will give me the full result with not only
the House Number, but the Street Name and Zip code?

I guess my main question is how to run the formula for multiple results? Am
I even going about this the right way?

Thanks for any help!!

--

Dave Peterson