Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual control of link updating for downloaded quotes? | Excel Discussion (Misc queries) | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
taking data from multiple cells and displaying in one cell | Excel Discussion (Misc queries) | |||
Multiple dates, times, or formulas in a single cell | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions |