What is ENCODEURL function in Excel? ENCODEURL function is one of Web functions in Microsoft Excel that returns a URL-encoded string. Notes: Microsoft introduced three new functions in Excel 2013 that will return data from a webservice. The ENCODEURL function is not available in Excel Online, and it is not available in Excel 2016 for Mac. NOTE: This function may appear…
Category: Web Functions
WEBSERVICE function: Description, Usage, Syntax, Examples and Explanation
What is WEBSERVICE function in Excel? WEBSERVICE function is one of Web functions in Microsoft Excel that returns data from a web service on the Internet or Intranet. Notes: Microsoft introduced three new functions in Excel 2013 that will return data from a webservice. The WEBSERVICE function is only available in Excel 2013 and Excel 2016 for Windows. NOTE: This function may…
FILTERXML function: Description, Usage, Syntax, Examples and Explanation
What is FILTERXML function in Excel? FILTERXML function is one of Web functions in Microsoft Excel that returns specific data from the XML content by using the specified XPath. Notes: Microsoft introduced three new functions in Excel 2013 that will return data from a webservice. The FILTERXML function is not available in Excel Online, and it is not available in Excel 2016…
How to remove trailing slash from url in Excel
To remove a trailing slash from a URL or path, you can use a formula based on the LEFT and LEN functions. Formula =LEFT(url,LEN(B4)-(RIGHT(url)=”/”)) Explanation In the example shown, the formula in cell C6 is: =LEFT(B4,LEN(B4)-(RIGHT(B4)=”/”)) How this formula works At the core, this formula uses the LEFT function to return text starting from the left. To work out how…
How to strip protocol and trailing slash from URL in Excel
To remove the protocol (i.e. http://, ftp://, etc.) and trailing slash from a URL, you can use a formula based on the MID, FIND, and LEN functions. Formula =MID(url,FIND(“//”,url)+2,LEN(url)-FIND(“//”,url)-1-(RIGHT(url)=”/”)) Explanation In the example shown, the formula in C5 is: =MID(B4,FIND(“//”,B4)+2,LEN(B4)-FIND(“//”,B4)-1-(RIGHT(B4)=”/”)) How this formula works The core of this formula is the MID function, which extracts the text in a URL…
How to get top level domain (TLD) in Excel
To extract the top level domain (called “TLD”) from a list of domain names or email addresses, you can use a rather complex formula that uses several functions. In the formula below, domain represents a domain or email address in normal “dot” syntax. Formula =RIGHT(domain,LEN(domain)-FIND(“*”,SUBSTITUTE(domain,”.”,”*”, LEN(domain)-LEN(SUBSTITUTE(domain,”.”,””))))) Explanation In the example, the active cell contains this formula: =RIGHT(B4,LEN(B4)-FIND(“*”,SUBSTITUTE(B4,”.”,”*”,LEN(B4)-LEN(SUBSTITUTE(B4,”.”,””))))) How the formula works:…
How to get page from URL in Excel
To extract the page, or the part of a path after the last forward slash (/), you can use a formula based on several Excel functions: TRIM, RIGHT, SUBSTITUTE, and REPT. Formula =TRIM(RIGHT(SUBSTITUTE(url,”/”,REPT(” “,100)),100)) Explanation In the example shown, the formula in C5 is: =TRIM(RIGHT(SUBSTITUTE(B5,”/”,REPT(” “,100)),100)) How this formula works At the core, this formula replaces all forward slashes (/)…
How to extract name from email address in Excel
If want to extract the name part of an email address, you can do so with a formula that uses the LEFT and FIND functions. In the formula below, email represents the email address you are working with. Formula =LEFT(email,FIND(“@”,email)-1) Explanation In the example, we are using this formula: =LEFT(C4,FIND(“@”,C4)-1) Here’s how the formula works: At the core, this formula…
How to extract domain from email address in Excel
Atimes a user may want to extract the domain from an email address, the RIGHT, LEN, and FIND functions can be used to achieve that. In the formula below, email represents the email address you are working with. Formula =RIGHT(email,LEN(email)-FIND(“@”,email)) Explanation In the example, we are using this formula: =RIGHT(C4,LEN(C4)-FIND(“@”,C4)) Here’s how the formula works C4 contains the email address…
How to create email address with name and domain in Excel
Atimes a user may want to build an email address from a first and last name. In that case, use a formula based on simple concatenation with help from the LEFT and LOWER functions. See illustration below: Formula =LOWER(LEFT(first)&last&”@”&domain) Explanation In the example shown, the formula in D5 is: =LOWER(LEFT(B5)&C5&”@”&$G$6) How this formula works Working from the inside out, this…
How to create email address from name in Excel
To build email addresses using first and last names, you can use a formula that concatenates values, with help from the LOWER and LEFT functions as needed. See example below: Formula =LOWER(LEFT(first)&last)&”@domain.com” Explanation In the example shown, the formula in D5 is: =LOWER(LEFT(C5)&B5)&”@”&”acme.com” How this formula works For a name like “Tim Brown”, this formula builds an email address like…
How to extract domain name from URL in Excel
If you want to extract the domain name from a complete URL, you can do so with a formula that uses the LEFT and FIND functions. See example: In the formula below, url is the the URL you are working with. Formula =LEFT(url,FIND(“/”,url,9)) Explanation In the example, we are using this formula: =LEFT(B4,FIND(“/”,B4,9)) Here’s how the formula works: B4 contains…
Complete List of Excel Web Functions References and Examples
Web Function was one of the functions Microsoft released in Excel 2013. Here’s a full list of Built-In Excel Web Functions in Excel and their Descriptions below: Click each of the function link to see detailed example. Web Functions ENCODEURL Returns a URL-encoded string (New in Excel 2013) FILTERXML Returns data from XML content, using a specified XPath (New in Excel 2013) WEBSERVICE…