In this tutorial, I will show you 2 methods to do this. And none of it would take more than a minute.
Method 1 – Using Text to Column
This is the easiest way if the data has a pattern. For example, in email id, there would always be a username, followed by ‘@’, and ends with the domain name. The trick is to extract the text before the @ sign. Here is how you can do this: This will give you the usernames from the email ids. Note that if you do not specify a destination cell in Step 3, the original data is overwritten with the extracted data (excel does warn you before overwriting). This method is super fast and you can have the list in a couple of seconds. However, it is not dynamic. If you add a record or make any changes in existing email ids, you will have to do this again.
Method 2 – Using Excel Formulas
Excel Formulas has the benefit of making the results dynamic. With formulas, if you change the email ids, the result would update automatically. Here is the formula you can use to do this: There are 2 parts to it:
FIND(“@”,A2,1) returns the position of @. In case of abc@gmail.com, it will return 4. Now we want to extract the text on the left of @, so we subtract 1 from this formula (which would return 3) LEFT(A2,FIND(“@”,A2,1)-1) extracts all the characters to the left of @
Whichever method you choose, it will not take you more than 2 minutes to do it. Mission Accomplished 🙂
Analyze Each Character of a Text String. How to Extract a Substring in Excel (Using TEXT Formulas). Extract Data from Drop Down List Selection in Excel. Separate First and Last Name in Excel (Split Names Using Formulas) How to Combine First and Last Name in Excel
i have written this function the pop up is showing “You’ve entered too many arguments for this function” Please help me My ID is ktogra@gmail.com This will of course modify the original records, so you might want to make a copy of the data first. But I still think it can be accomplished in under 2 minutes. 🙂
