How to merge sheets in excel - javatpoint - Free Printable

Problem Description:

The image shows two Excel workbooks:
1. Workbook1: Contains a list of items (e.g., "Tea," "Coffee," "Cold Drink") in column A.
2. Workbook2: Contains a table with two columns:
- Column A: Email Addresses
- Column B: Domain Names

The task is to extract the domain names from the email addresses in Workbook2 and populate them in Column B.

Solution Approach:

To solve this problem, we need to extract the domain names from the email addresses in Column A of Workbook2. The domain name is typically the part of the email address that comes after the "@" symbol. We can achieve this using Excel's built-in functions.

#### Steps to Solve:
1. Identify the Structure of Email Addresses:
- An email address has the format: `[email protected]`.
- The domain name is everything after the "@" symbol.

2. Use Excel Functions:
- `RIGHT` Function: Extracts characters from the right side of a string.
- `FIND` Function: Finds the position of the "@" symbol in the email address.
- `LEN` Function: Calculates the total length of the email address.

3. Formula to Extract Domain Name:
The formula to extract the domain name from an email address is:
```excel
=RIGHT(A2, LEN(A2) - FIND("@", A2))
```
- `FIND("@", A2)` locates the position of the "@" symbol in the email address.
- `LEN(A2)` gives the total length of the email address.
- `LEN(A2) - FIND("@", A2)` calculates the number of characters after the "@" symbol.
- `RIGHT(A2, ...)` extracts those characters starting from the right.

4. Apply the Formula:
- Enter the formula in cell B2 (next to the first email address).
- Drag the formula down to apply it to the rest of the cells in Column B.

Detailed Explanation:

- Step 1: Open Workbook2.
- Step 2: Click on cell B2 (the first cell in the "Domain Name" column).
- Step 3: Enter the formula:
```excel
=RIGHT(A2, LEN(A2) - FIND("@", A2))
```
- Step 4: Press Enter. This will extract the domain name for the email address in cell A2.
- Step 5: Click on the bottom-right corner of cell B2 (the fill handle) and drag it down to fill the formula for the rest of the cells in Column B.

Example Calculation:

For the email address in cell A2: `[email protected]`:
- `FIND("@", A2)` returns `12` (position of "@" in the string).
- `LEN(A2)` returns `21` (total length of the string).
- `LEN(A2) - FIND("@", A2)` returns `9` (number of characters after "@").
- `RIGHT(A2, 9)` extracts `yahoo.com`.

Final Output:

After applying the formula, Column B will be populated with the corresponding domain names:
| Email Address | Domain Name |
|---------------------|----------------|
| Joshaf1207 | yahoo.com |
| oliver.eva | gmail.com |
| william.sofiya | yahoo.com |
| mac342 | microsoft.com |
| alexander | gmail.com |
| lucasima | gmail.com |

Conclusion:

The problem is solved by using the `RIGHT`, `LEN`, and `FIND` functions in Excel to extract the domain names from the email addresses. The final answer is:

\boxed{=RIGHT(A2, LEN(A2) - FIND("@", A2))}