THE PROBLEM: You are given a list of email addresses and you wish to extract the First Name, Surname and Company Name from the list. The majority of the emails come in the format firstname.surname@company.com. Separating the information manually will be time consuming; you need a quick solution.
THE SOLUTION: Use the Text to Columns function as per the screenshots below…
>> Highlight the column of emails
>> Click Data from the Menu Bar, then Text to Columns (or keyboard shortcut Alt > d > e)
>> On the dialogue box that appears, select the “delimited” option and click Next
>> Click the “Other” box and enter a full stop into the space provided. This tells Excel to create a new column each time it encounters a full stop in the data field
>> On the next dialogue box, select the cell where you would like Excel to start entering the segregated columns and click Finish
>> Excel separates the data as per your instructions. There were two full stops in each email address, therefore Excel has created 3 new columns of data.
>> To finally separate the Final Column (Column C) between the surname and company name, repeat the process, only this time on the second dialogue box replace the full stop with the @ symbol and on the third dialogue box, select a new destination cell, in this example, cell E1
>> You have now created First Name, Surname and Company Name fields for each email address.
THE SOLUTION: Use the Text to Columns function as per the screenshots below…
>> Highlight the column of emails
>> Click Data from the Menu Bar, then Text to Columns (or keyboard shortcut Alt > d > e)
>> On the dialogue box that appears, select the “delimited” option and click Next
>> Click the “Other” box and enter a full stop into the space provided. This tells Excel to create a new column each time it encounters a full stop in the data field>> On the next dialogue box, select the cell where you would like Excel to start entering the segregated columns and click Finish>> Excel separates the data as per your instructions. There were two full stops in each email address, therefore Excel has created 3 new columns of data.>> To finally separate the Final Column (Column C) between the surname and company name, repeat the process, only this time on the second dialogue box replace the full stop with the @ symbol and on the third dialogue box, select a new destination cell, in this example, cell E1>> You have now created First Name, Surname and Company Name fields for each email address.
Posts
No comments:
Post a Comment