Tricky Text to Columns


Anyone know how to use Text to Columns when the underscore in the data is used as both a space and part of an email address?  I know an extra step is involved, I just can't put my finger on it.

Your manager has given you raw contact info that needs to be separated by First Name, Last Name, Email Address and Employee Level. Using the text to columns function, separate this data in the table below.

Eric_Johnson_johnson_eric@company.com_Manager     John_Smith_john.smith@company.com_Director     Beth_Summers_beth.summers@company.com_associate   Miles_Peterson_Miles.peterson@company.com_Partner   Jenna_Stone_Jenne_Stone@company.com_Manager     Quinn_Cook_Quinn_Cook@company.com_Manager    



If you're gonna post homework, leave out the part that identifies it as homework lol.

In this case I'll help you out. 

Do text-to-columns using the underscore and then, since each email and name follows the same pattern, you can use concatenation to put the pieces back together.

If you have an example in cell A1 and text-to-columns it, you can recreate the email address using a formula like this: =C1 & "_" & D1 where C1 has the first part of the email and D1 the last part.

Since some of the examples use periods instead of underscores in the example, you will also have to account for that, not a big deal as the ones with underscores will take up an extra column after using text-to-columns so you can just filter by that extra column and apply the correct formula for each email type.

That said, tell your teacher that, in the real world, data won't be so clean as this haha.


Answer the Question

You must create an account to use the forum. Create an Account or Login