In this tutorial, we will learn how to get domain name from URL in Excel. In order to do this, we will use LEFT and FIND functions. Combination of these two functions allows us to get a number of characters to the specific certain character.
Figure 1. Final result
Syntax of LEFT and FIND function
The generic formula to get domain name from URL is:
In order to get domain name from URL, we will have to use LEFT and FIND functions. Therefore, we will first explain both functions separately.
FIND function returns a position of a specific text in a cell.
The parameters of this function are:
- find_text – a text which we want to find in a cell
- within_text – a text cell in which we want to find a position of a character
- start_num – a position (character number) from which we want to look up for a text. This is a non-mandatory parameter.
LEFT function returns a certain number of characters from the left side in a cell.
The parameters of the function are:
- text – a cell from which we want to take substring
- num_chars – a number of characters which we want to take from the left side. This is a non-mandatory parameter.
Finally, The result of the FIND function will be a num_chars parameter for the LEFT function.
Setting up Your Data
Figure 2. The structure of the data
We will now explain the example. Let’s start with examining the structure of the data that we will use.
In column B (“URL”) we have URL, while in column C (“Domain”) we want to get domain from URL. A domain is a link from the beginning until the third “/”.
Figure 3. Get domain name from URL
Retrieve Domain Name from URL
The formula looks like:
We will first evaluate the FIND part of the function, because we want to find the position of “/” in B3 cell beginning from the 9th character in B3. This is because the first two occurrences of “/” in B3 are included in the domain, while the third one is the end of the domain. Therefore, the result of
FIND("/",B3,9) is 22.
This result will be the num_chars parameter of the LEFT function. So, we will have
=LEFT(B3,22) function and take 22 characters from the left from B3, which represents the domain.
Above all, to get domain name from URL, we need to follow these steps:
- Select cell C3 and click on it
- Insert the formula
- Press enter
- Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell