Encodeurl Function

Purpose

Return value

Syntax

=ENCODEURL(text)

• text - The text to be encoded.

Using the ENCODEURL function

The ENCODEURL function returns a URL-encoded string composed of US-ASCII characters. URL encoding, sometimes called “percent encoding” is a method of encoding characters in a URL using only legal US-ASCII characters. Some characters cannot be part of a URL and are “reserved”. Only characters that are reserved are encoded by ENCODEURL; other characters are left untouched. Common reserved characters include the space character (" “), the forward slash “/”, the hash character (#) and others as shown in the example above.

Example

To use ENCODEURL, supply text or a cell reference that contains text. In the example below, ENCODEURL is used to encode the text “Hello World!”

=ENCODEURL("Hello World!") // returns "Hello%20World%21"

In the example at the top of the page, the formula in cell C5, copied down is:

=ENCODEURL(B5)

At each new row, ENCODEURL returns the encoded text from column B.

Reserved characters

The table below shows a list of reserved characters and their url-encoded equivalent.

Purpose

Return value

Syntax

=FILTERXML(xml,xpath)

• xml - Valid XML as a text string.
• xpath - A valid Xpath expression as a text string.

Using the FILTERXML function

The Excel FILTERXML function returns specific data from XML text using a specified XPath expression.

XML is a text format for storing and transporting data. It is not dependent on any particular hardware or software. XML is extensible and is designed to transport data, as opposed to displaying data in a particular way. XML has strict syntax rules which allows software to traverse the structure of an XML document and perform various operations.

XPath is a special query language for selecting the elements and attributes in an XML document. The FILTERXML function uses XPath to match and extract data from text in XML format.

FILTERXML is only available in Excel for Windows, not Excel for Mac, or Excel Online.

Example

In the example shown, the cell contains XML that carries information about albums published as CDs. Each CD contains the title of the album, the name of the artist, and the year the album was released. The formula in cell D5 uses FILTERXML to extract all titles:

=FILTERXML(B5,"//cd/title")

The xml argument is the XML in cell B5, and the xpath argument is the expression “//cd/title”, which matches all title elements with the parent . In Excel 365 , which supports dynamic arrays , the results spill into the range D5:D14 automatically.