One of the common tasks in Excel is to modify email addresses or other text strings that follow a similar pattern, such as replacing all text before the "@" sign in an email address. Whether you’re cleaning up data or updating email addresses for multiple contacts, you can easily accomplish this task using Excel’s built-in text functions. In this article, we will explore how to select all text before the "@" sign in a cell and replace it with something else.

Using the SUBSTITUTE Function for Replacing Text
The simplest way to replace all the text before the "@" symbol in Excel is by using the SUBSTITUTE function in combination with other text functions. Excel’s SUBSTITUTE function allows you to replace specific text within a string. To target only the portion of the string before the "@" sign, you can use a combination of the LEFT, FIND, and SUBSTITUTE functions.
The process begins by identifying the position of the "@" symbol within the text string using the FIND function. FIND returns the position of a specified character within a string. Once you have the position of the "@" symbol, you can use this position to extract the portion of the string before the "@" and replace it with new text.
The Formula Breakdown
Let’s break down the formula:

1. FIND("@", A1): This function finds the position of the "@" sign within the text in cell A1. For example, if the email address is "john.doe@example.com," the FIND function will return 9 because the "@" is the 9th character in the string.
2. LEFT(A1, FIND("@", A1) - 1): The LEFT function is used to extract a portion of the text string starting from the left. In this case, it extracts everything to the left of the "@" symbol by taking all characters up to the position found by the FIND function (minus 1 to exclude the "@" itself).
3. SUBSTITUTE(A1, LEFT(A1, FIND("@", A1) - 1), "newtext"): The SUBSTITUTE function will then replace the portion of the string before the "@" (found using the LEFT and FIND functions) with the new text you want to use. In this case, you would replace everything before the "@" with the word "newtext."
   For example, if the email address in cell A1 is "john.doe@example.com," the formula =SUBSTITUTE(A1, LEFT(A1, FIND("@", A1) - 1), "newtext") would return "newtext@example.com."
   General Approach for Replacing Text Before the "@" in Any Cell
   To apply this solution to an entire column or multiple rows, you can enter the formula in the first row of the column where you want the modified text to appear and then drag the formula down to fill the rest of the cells in that column. This method ensures that each email address in the column has the portion before the "@" sign replaced with your chosen text.
   For example, if you want to replace all text before the "@" in column A with "username," you would enter the following formula in the first row of column B (assuming the email addresses are in column A):
   =SUBSTITUTE(A1, LEFT(A1, FIND("@", A1) - 1), "username")
   Then, drag the formula down to the rest of the cells in column B to replace the text in every corresponding row.
   Using Excel's Find and Replace Feature
   If you don’t want to use a formula and prefer a more straightforward approach, you can take advantage of Excel’s built-in Find and Replace feature. While Find and Replace does not offer the ability to target text before a specific character like the "@" symbol directly, you can use wildcards to find text patterns and replace them.
   Here’s how to use Find and Replace to target text before the "@" symbol:
4. Press Ctrl + H to open the Find and Replace dialog box.
5. In the "Find what" field, enter the wildcard pattern that will match everything before the "@" symbol. The pattern you can use is: (@). This pattern tells Excel to find any text string followed by an "@" and then the remaining text after the "@".
6. In the "Replace with" field, enter the text you want to replace everything before the "@" with, followed by @ (to retain the domain part of the email). For example, if you want to replace the portion before the "@" with "newuser", you would type newuser@ in the "Replace with" field.
7. Click Replace All to apply the changes to all the cells in the selected range.
   While this method is less precise than using a formula, it is a quick way to replace the text before the "@" for all occurrences in the selected cells.
   Important Considerations
   While these methods work well for most cases, there are a few things to keep in mind:
   • Ensure that the cells you are working with contain valid text strings with an "@" symbol. If there are any entries without an "@" symbol, the FIND function will return an error, so you may need to handle these cases with error-checking functions like IFERROR.
   • If there are any variations in the data, such as additional spaces before or after the "@" symbol, you may need to use the TRIM function to remove those extra spaces before applying the formula.