Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Tricky Text to Columns

0

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    
Answer
Discuss

Answers

0

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.

Discuss


Answer the Question

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