Creating a Series by Changing One Part of Cell within A String

[tarheel01]

Hello,

I would like to create a long string of observations in excel, each of which is a website, via automation. The basic structure of the URL is:

https://www.congress.gov/bill/107th-congress/house-bill/1/all-actions-with-amendments?pageSort=asc

The change from one website to another is the "1" in the middle of the URL ("house-bill/1/all") will become a "2" and so on. The string will look like this, but with thousands of observations:

https://www.congress.gov/bill/107th-congress/house-bill/1/all-actions-with-amendments?pageSort=asc

https://www.congress.gov/bill/107th-congress/house-bill/2/all-actions-with-amendments?pageSort=asc

https://www.congress.gov/bill/107th-congress/house-bill/3/all-actions-with-amendments?pageSort=asc

Is there an automated means through which I can do this? I would like to set it such that the next row takes the value of the row above and adds one, but I don't see a way to do this. Any help would be greatly appreciated, and I apologize if this question has been asked before.

 

[GiveMeABreak]

I would split the URL into 2 parts, before the number (in first column) and after the number (in third column). I would then make a list of all the numbers you need in the URL 1-1000 or however many, in the second column. The attached image goes up to 5. Then in the fourth column, I would concatenate the first three columns A1&B1&C1. Make sure you lock down A & C with the $ unless you want to repeat A1 and C1 all the way down.

 

[JoeMo]

What do you want to do with the string after its created? If you put it in a single cell it cannot contain more than 32,767 characters. Might that be a problem?

 

[tarheel01]

Each cell would be one URL. Therefore, the end dataset would be 1xN, where N=The number of observations. The character limit is not a problem, and sorry for any confusion.

 

[GiveMeABreak]

sorry, image didn't post correctly. Hopefully this will work:

 

[JoeMo]

Here's a macro that will list each of the N URLs in column A, starting in A1.

Sub tarheel01()
Const prefix As String = "https://www.congress.gov/bill/107th-congress/house-bill/"
Const suffix As String = "/all-actions-with-amendments?pageSort=asc"
Dim N As Variant, i As Long
N = Application.InputBox("How many URLs?", Type:=1)
If N = False Then Exit Sub
Application.ScreenUpdating = False
For i = 1 To N
    Range("A" & i).Value = prefix & i & suffix
Next i
Columns("A").AutoFit
Application.ScreenUpdating = True
End Sub

 

[tarheel01]

Thanks for the help! GiveMeABreak's solution works, and my VBA editor isn't letting me put the entire macro in, so I will go with that. Thanks again.

 

[ukmikeb]

And if you wanted to use one column instead of four :-
tarheel01[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]https://www.congress.gov/bill/107th-congress/house-bill/1/all-actions-with-amendments?pageSort=asc[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]https://www.congress.gov/bill/107th-congress/house-bill/2/all-actions-with-amendments?pageSort=asc[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]https://www.congress.gov/bill/107th-congress/house-bill/3/all-actions-with-amendments?pageSort=asc[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2007

Cell	Formula
A1	="https://www.congress.gov/bill/107th-congress/house-bill/"&ROW()&"/all-actions-with-amendments?pageSort=asc"

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

and dragged down.


hth