A lot of time spent in Excel is used to clean up data that may not be immediately useful into something valuable. Being able to break up a large text cell into more actionable pieces is extremely helpful.
Contents:
Formula
This formula will return everything to the right of the "@" character from cell A1.
= RIGHT(A1, LEN(A1) - SEARCH("@",A1))If you have the latest version of Excel, you could use one of the new formulas TEXTAFTER to return everything after a specific character as well:
= TEXTAFTER(A1,"@")Explanation
To return everything to the right of a specific character in a cell in Excel, we'll need to use the RIGHT function and the SEARCH function together.
Here is the syntax for the RIGHT function:
RIGHT(text, [num_chars])The text argument is the cell that you want to extract the characters from. The num_chars argument is optional and specifies the number of characters to extract. If you omit this argument, Excel will return all the characters to the right of the specific character.
Here is the syntax for the SEARCH function:
SEARCH(find_text, within_text, [start_num])The find_text argument is the specific character that you want to find. The within_text argument is the cell that you want to search for the specific character. The start_num argument is optional and specifies the character at which to start the search. If you omit this argument, Excel will start the search at the first character of the cell.
We can combine these functions, returning everything to the right of a specific character in a cell with the following formula:
= RIGHT(A1, LEN(A1) - SEARCH("@", A1))In this example, A1 is the cell that you want to extract the characters from, and "@" is the specific character that you want to find. The SEARCH function returns the position of the "@" character in the cell, and the LEN function returns the total number of characters in the cell. The RIGHT function then extracts all the characters to the right of the "@" character by subtracting the position of the "@" character from the total number of characters in the cell.
Note:
This formula will only work if the specific character is present in the cell. If the character is not present, the SEARCH function will return an error. To avoid this error, you can use the IFERROR function to return a default value if the SEARCH function returns an error. For example:
= IFERROR(RIGHT(A1, LEN(A1) - SEARCH("@", A1)), "Character not found")This formula will return everything to the right of the "@" character if it is present in the cell, and will return the text "Character not found" if the "@" character is not present in the cell.
Examples
1. How to Extract Last Name from Full Name
If we have a list of full name in column B, and want to pull out the last name along side it, we can use the following formula:
= RIGHT(B2, LEN(B2) - SEARCH(" ",B2))This formula will extract everything after the space, which in this case, is the last name.
If the full name is separated by a comma "," or a comma and a space ", " you can switch out the space in the above formula to get the same results.
If the character or group of characters that you are searching for does not exist in the name, a #VALUE error will be returned.
2. How to Extract the Domain from Email
Similarly, if we have a list of email addresses in column B, and want to pull out the email domain associated with each address, we can use the following formula:
= RIGHT(B2, LEN(B2) - SEARCH("@",B2))This formula will extract everything after the "@" sign, leaving you the email domain name and removing the username.