Replacing numbers into a string?

silvergrahm,

Welcome to the Excel Forum.

The only 5 character zip code on Sheet2 is 02133.

There is no 5 character zip code 02133 in Sheet1.

Are you looking to change the 5 character zip code on Sheet2 to 02133-#### ?

If so, then please post another workbook that contains in Sheet1 the five character and the 02133-#### zip codes.

Then it will be easy to do what you require.

Hi Stan, I have excel 03 for Mac, so perhaps something is not working? I downloaded my own attachment and it works for me, the way I saved it. Does sheet 1 show a list of website addresses that are exactly the same? Does sheet 2 show a list of 5 digit numbers?

Let me explain what I want to do a little bit better. I am trying to (ethically, w/ low bandwidth) "scrape" data from one of those online locators with an embedded map. I have found that I can make a scraper in Outwit Hub that will harvest the info I need from each page that is generated per zip code query. So, I figure I will be able to get all of the info I need if I compile a list of URLs for that page that correspond to thousands of searches, one for each US zip code, and then run my scraper on each of these URLs, one by one.

I'm not going to show you which exact URL I want to scrape, so I am using an example URL that is the same as the real site I am talking about for all folders except the actual domain. That example is: http://hermanmiller.com/dealer-locat...&product_name= Don't bother clicking it because it doesn't exist.

The 5 character string exists in red here: http://hermanmiller.com/dealer-locator?location=#####&product_name= On this particular URL, you can input any five digit US zip code, and the site will return a search for dealers within and around that geographic location. I was just using 02133 as an example.

I want to make a macro that will make me a list of URLs that are equivalent to me searching every single five digit US zip code on that site. I figure the best way to do this is to have one sheet with the URL, and another with a list of us Zip codes. I need excel to locate the five character string in the URL above (#####) and replace it with a real zip code, and then do it over again for another zip code, and then another. I know it must be fairly easy, but I'm at a loss how to even start.

EDIT: The site automatically truncated the URL, apparently. You can view the full URL if you open it in a new window.

Hello silvergrahm,

This macro will take the single URL in "A1" on "Sheet1" and add the ZIP to it from "A1" on "Sheet2". The URL will be copied into "A2" on "Sheet1" and the ZIP from "A2" "Sheet2" will be inserted. This will continue until there are no more ZIP codes on Sheet2.

A button has been added to "Sheet1" to run the macro.

NOTE: Sorry, I just realized this for a MAC. The Mac does not support Regular Expressions and this code will not run.

Please Login or Register  to view this content.