I am creating a spreadsheet of bills filed at the Texas Legislature. In one column, I am listing the bill numbers such as HB761. Each bill number has a corresponding webpage that I would like to link to- for demonstration purposes, we will say this links back to www.texas.state/housebill761. The URL is the same each time excepting the bill number at the end is changed. Is there any way to be able to enter just the bill number in and have it automatically hyperlink to the appropriate page based on the bill number given?
2
- Welcome to Super User. We are happy to help, but are not here to do it for you. Please add details of what you have attempted so far (include any formulas you've tried) and where have you gotten stuck. We will help from there.CharlieRB– CharlieRB2016-12-02 21:17:56 +00:00Commented Dec 2, 2016 at 21:17
- I have no idea how to even begin. I don't know anything about hyperlinking automatically. I don't think it uses formulas, so I haven't tried them. I tried checking on autocompletion for any possibilities there, and it's already set to autocomplete if I type in a URL, but I'm just trying to type in a number and have the column autocomplete it into a hyperlink. I don't even know if this is a possibility.Nakia– Nakia2016-12-02 21:30:51 +00:00Commented Dec 2, 2016 at 21:30
Add a comment |
1 Answer
You can use a combination of the functions HYPERLINK, CONCATENATE and RIGHT to achieve what you are looking for.
HYPERLINK creates a clickable link. This will be used with the combination of the below functions.
CONCATENATE combines text strings. I your case, this will combine the first part to the hyperlink text ""http://www.texas.state/housebill" with the results of the RIGHT function below.
RIGHT extracts the specified number of characters to to the right of a string. In your case, we want the 3 numbers to the right in the HB761. This results in 761.
In my example below, the HB761 is in cell A1. Change that to the cell you need. The last part to the HYPERLINK formula can be modified to read what you like. I just used "Link" for the example.
=HYPERLINK(CONCATENATE("http://www.texas.state/housebill", RIGHT(A1, 3)), "Link") Once this formula is placed, you can copy it down the column to suite your needs.
- Thank you for your help! This did almost everything that I wanted it to do! There is no way that I was was coming close to putting this together even after three days of working on it. Thank you again!Nakia– Nakia2016-12-12 17:56:08 +00:00Commented Dec 12, 2016 at 17:56
- Glad to help. To let others know this answered your question, please select the check-mark under the voting buttons on the left.CharlieRB– CharlieRB2016-12-12 22:31:03 +00:00Commented Dec 12, 2016 at 22:31