Merging links in Google Sheets

In this article, I will talk about two functions that, in combination, can combine several links into one. This will come in handy when the base of the link is the same, but you need to substitute different endings to get to specific pages. This is useful for tracking packages, viewing user profiles by ID, or getting a result for a specific request. Below you will get all the information on this topic and understand in what situations this combination of functions can come in handy.

Analysis of the functions used

As part of the task, we will use a function to declare a regular link and a function to combine multiple values ​​within a single cell. Let’s deal with all this in order to understand the effect of each individual function.

  1. First, let’s declare the function =HYPERLINK( . Of course, you can make the selected text a link through a separate button on the toolbar, but in this case this option is not entirely suitable.
  2. In double quotes, enter the link you want to click on.
  3. Please note that now the line has changed its color, and the inscription has become clickable.
  4. To replace the main link with text, put a semicolon after it, open the quotation marks again and enter the desired caption.
  5. Now, instead of a large link, there will be a concise text that does not negatively affect the appearance of the table.

The next function is CONCATENATE . It is responsible for combining the values ​​of different cells into one, which will free the user from having to do it himself. Let’s look at a simple example of how this function works:

  1. Below you can see how values ​​are located in two different cells that you would like to combine into one cell.
  2. To do this, in any convenient place, declare the function =CONCATENATE() . In parentheses, separated by a semicolon, specify these two cells in sequence and press Enter to confirm the action.
  3. Pay attention to how two values ​​are concatenated in the cell with the function. This is exactly what we will achieve when combining several links into one.

Combining multiple links into one

Now let’s get down to the main task – combining several links into one. Let’s look at the original data. A separate list shows the values ​​that should be at the end of the link. These can be TTH numbers, IDs, or any expressions that fit into the main link.

The main link must be specified in any convenient place, since you will have to refer to it later. Be sure to leave it as is and don’t add text to replace it, otherwise it won’t work.

Let’s start writing the necessary formula.

  1. In the column to the right of the list of values, declare =CONCATENATE( .
  2. First enter the cell number with the main link, and after a semicolon – the second part.
  3. Press Enter and see what happens in the end. Now you have a link, but not working yet.
  4. I added the postscript “Socialism” to the main Wikipedia in order to get to the page of this concept during the transition. You can specify any ending depending on what you are concatenating for.
  5. Now let’s make the link look normal and readable so that you can follow it directly from the table. To do this, declare the HYPERLINK function at the beginning of the line .
  6. At the end, add a semicolon, in double quotes, specify any text convenient for you, close the bracket and press Enter .
  7. You have a convenient link leading to the required page.
  8. It remains to stretch it to the entire list with values, but first, the root of the link will need to be fixed so that the cells do not shift when stretched. To do this, use a constant, that is, the $ sign between the cell letter.
  9. Hold the first cell by the lower right corner and drag it down.
  10. The stretch was successful, and now you have different links with relevant pages that you can click on and get the relevant information.

So, with the help of two simple functions, we managed to combine several links into one in order to optimize the transition through them and make it easier to get the necessary information. You can use the CONCATENATE function for other purposes, you just need to understand what effect this tool gives and where it will be appropriate. 

Leave a Reply

Your email address will not be published.