We are working in Excel 2003.

We need to find a way to search and replace two instances of "ws" in a
whole column of link code. There are 2 occurrences of "ws" in each
link that need to be replaced. The difficult part is that we need to
insert a different code in place of "ws" for each row in a sequence.
If we replace "ws" with "version1400-20" in row 2 then we would want
to replace "ws" with "version1401-20" in row 3 and so on in an exact
sequence.

Original Link example:
http://www.amazon.com/exec/obidos/redirect?tag=ws%26link_code=xm2%26camp=2025%26creative=165953%26path=http://www.amazon.com/gp/redirect.html%253fASIN=B0007ZK364%2526tag=ws%2526lcode=xm2%2526cID=2025%2526ccmID=165953%2526location=/o/ASIN/B0007ZK364%25253FSubscriptionId=0XXJ058D0Y0WNYZG7QR2

Modified example row 2:
http://www.amazon.com/exec/obidos/redirect?tag=version1400-20%26link_code=xm2%26camp=2025%26creative=165953%26path=http://www.amazon.com/gp/redirect.html%253fASIN=B0007ZK364%2526tag=version1400-20%2526lcode=xm2%2526cID=2025%2526ccmID=165953%2526location=/o/ASIN/B0007ZK364%25253FSubscriptionId=0XXJ058D0Y0WNYZG7QR2

Modified example row 3:
http://www.amazon.com/exec/obidos/redirect?tag=version1401-20%26link_code=xm2%26camp=2025%26creative=165953%26path=http://www.amazon.com/gp/redirect.html%253fASIN=B0007ZK364%2526tag=version1401-20%2526lcode=xm2%2526cID=2025%2526ccmID=165953%2526location=/o/ASIN/B0007ZK364%25253FSubscriptionId=0XXJ058D0Y0WNYZG7QR2

---

Clarification of Question by alpacaherder-ga on 03 Jan 2006 14:56 PST

To clarify - we just need a way to do search and replace of one thing
with a sequential series of numbers. Once we are able to do this we
can run it twice to do the work we need. I'm hoping that somebody
knows a way to do sequential search and replace in excel (or access
for that matter).

---

Request for Question Clarification by maniac-ga on 09 Jan 2006 17:20 PST

Hello Alpacaherder,

Since you are satisfied with the comment, I suggest you close the question.

  --Maniac

OK, this is not terribly hard to do.

Suppose you have your link addresses in column A, starting at row 2.
In cell B2, enter this formula:
=SUBSTITUTE(A2, "ws", "version" & ROW(B2)+1398 & "-20")
This will give you the new link. Fill this down to the end of your
data and the correct link will be given in each cell.

Reason that it works: 
 - the SUBSTITUTE function takes a string and replaces all occurences
of a given substring within it by the supplied text. In this case, we
are taking the link in A2 and replacing all occurrences of "ws" with
the result of the formula:
"version" & ROW(B2)+1398 & "-20".

 - the ROW function returns the row number of the indicated cell. If
we enter =ROW(B2) in cell B2 and fill down, we will get =ROW(B3) in
cell B3 and so on. So in each cell, we are looking at the row number
of that cell and adding 1398 (so that we start at 1400 in cell B2). We
stick "version" before it and "-20" after it and we have what you
want.

- Note that at cell B102 you will get "version1500-20", at cell B602
you will get "version2000-20" and at cell B8602 you will get
"version10000-20". If this is not desired, you need to specify what
you want to happen when we hit these limits.

Thank you Manuka-ga, your solution worked perfectly.