How to Use ENCODEURL in Google Sheets to Create Clean URLs

Published on

In Google Sheets, we can use the ENCODEURL function to encode text for use in the query string of a URL efficiently. This web category function helps ensure that special characters are properly formatted and can be safely included in a web address. Let’s first understand the query string.

A URL consists of several parts, including:

  • Protocol: The communication method (e.g., https://)
  • Domain name: The website’s address (e.g., example.com)
  • Path: The specific page or resource (e.g., /products/)
  • Query string: Optional data sent to the server (e.g., ?q=search+term)
  • Fragment identifier (or hash): Optional reference to a section within the page (e.g., #section1)

In the URL https://infoinspired.com/#search/q=xlookup%20function, we can break down the components as follows:

  • Protocol: https://
  • Domain name: infoinspired.com
  • Path: /
  • Fragment identifier: #search/q=xlookup%20function

The query string itself is part of the fragment identifier and consists of q=xlookup%20function.

The encoded text, such as the search term used in the query string, is xlookup%20function. We can use the ENCODEURL function in Google Sheets to dynamically generate this part of the URL within a Google Sheets formula.

Key points:

Query string vs. fragment identifier: The query string is processed by the web server, while the fragment identifier is handled by the browser.

ENCODEURL Function and Character Encoding

Syntax:

ENCODEURL(text)

Argument:

  • text: The text to be encoded (required).

When special characters are present in your query string, encoding them is necessary to safely include them in a URL. The ENCODEURL function handles the character encoding aspect in Google Sheets.

The encoding convention involves representing each special character with a % sign followed by two hexadecimal digits.

Common Encodings:

  • Space: %20
  • Forward slash (/): %2F
  • Ampersand (&): %26
  • Equal sign (=): %3D
  • Question mark (?): %3F
  • Plus sign (+): %2B
  • Other special characters have corresponding percent-encoded values.

Examples of ENCODEURL in Google Sheets

We can hardcode the text (part of the query string) within the formula or refer to a cell. Let’s start with a hardcoded example:

=ENCODEURL("North, South, and West") // Returns "North%2C%20South%2C%20and%20West"

You can enter this text, i.e., “North, South, and West,” in a cell, for example, in A1, and refer to that using the ENCODEURL formula as follows:

=ENCODEURL(A1)

If you want to encode multiple texts, use the ARRAYFORMULA function with the ENCODEURL formula:

=ArrayFormula(ENCODEURL(A1:A3))
ENCODEURL Function Basic Example in Google Sheets

How to Use the ENCODEURL Function in Real-Life Scenarios with Google Sheets

Let’s consider a scenario where you want to search this blog for various Google Sheets-related queries. Let’s see how to use Google Sheets to generate the URLs dynamically.

Steps:

  • Enter the following URL in cell B2: https://infoinspired.com/#search/
  • Then, enter the search terms one by one in cells C2:C, for example, “xlookup” in C2, “map lambda function” in cell C3, and so on.
  • Enter the following formula, where ENCODEURL is part of it, in cell D2 and drag it down.
=$B$2&"q="&ENCODEURL(C2)

Formula Breakdown:

  • $B$2: The reference of the cell containing the base URL. Dollar signs are used to lock both its row and column, ensuring it stays fixed when the formula is copied.
  • "q=": A literal string used to specify the “query” parameter in the URL’s query string.
  • ENCODEURL(C2): Encodes the text (search term) in cell C2, replacing special characters with their percent-encoded equivalents for URL compatibility.
  • & (Ampersands): Used to join or concatenate the different parts together into a single text string.

Alternatively, you can use the following array formula in cell D2, which does not require dragging it down.

=ArrayFormula($B$2&"q="&ENCODEURL(C2:C4))
ENCODEURL Function Real-Life Example in Google Sheets

These are hyperlinks. Click on them to open the URL in your browser and search this site. You can use the ENCODEURL function in similar scenarios in Google Sheets.

Remember:

Monitor for any necessary updates to the formula or base URL, as website URLs and search functions might evolve. Adapt the formula to match your specific blog’s query string structure.

Conclusion

From the above examples, you can understand two important points about the use of the ENCODEURL function in Google Sheets.

Remember that ENCODEURL is specifically for encoding text within the query string of URLs, not entire URLs. Please refer to the formula in cell D2 above.

It’s not always necessary to encode pre-formatted, static URLs (commonly used for permanent links, and navigation menus), such as https://infoinspired.com/category/google-docs/.

If you want to see one more real-life use of the ENCODEURL function, please check my guide on pre-filling Google Docs Forms from Google Sheets.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.