Cell Data Split

[Data_Analyst]

Hi,

I am currently importing information from outlook into a single excel cell.

The content of the email looks like the following -

--------------------------------------------
REGISTRATION INFO
--------------------------------------------
John Doe
123 Excel St.
New York, NY
USA 12345
123 456 7890
johndoe@email.com

Gender: Male
How did you hear about us?: Google.com

--------------------------------------------
REGISTRANTION INFO
--------------------------------------------
Jane Doe
123 Excel St.
New York, NY
USA 12345
123 456 7890
janedoe@email.com

Gender: Female
How did you hear about us? Yahoo.com
John Smith
Owner
MyCompany
www.myfirstcompanyname.com
www.mysecondcompanyname.com

===================

All this information is in a single cell. What I am trying to do is separate the content of each line in that cell to a separate adjacent cell, but with only certain data such as the name, address, phone, and email - so it looks like the following -

Example -

John Doe | 123 456 7890 | johndoe@email.com
Jane Doe | 123 456 7890 | janedoe@email.com

The "|" represents a different column (name is column B, phone number is column C, email is column D - we would like to keep the original contents of the email in column A)

Sometimes the email can come multiple contacts and that needs to be in a separate row as shown in the example above.


Any help with this would be greatly appreciated.

Thanks!

 

[drsarao]

Assuming the Name, Phone No and Email ALWAYS come in 3rd, 7th and 8th line (as given in the 2 examples), try this:

Excel Workbook
	A	B	C	D
1	-------------------------------------------- REGISTRATION INFO -------------------------------------------- John Doe 123 Excel St. New York, NY USA 12345 123 456 7890 johndoe@email.com Gender: Male How did you hear about us?: Google.com	John Doe	123 456 7890	johndoe@email.com
2	-------------------------------------------- REGISTRANTION INFO -------------------------------------------- Jane Doe 123 Excel St. New York, NY USA 12345 123 456 7890 janedoe@email.com Gender: Female How did you hear about us? Yahoo.com John Smith Owner MyCompany www.myfirstcompanyname.com www.mysecondcompanyname.com	Jane Doe	123 456 7890	janedoe@email.com
Sheet2

Excel 2003

Cell Formulas
Range	Formula
B1	=MID(A1,FIND("@@",SUBSTITUTE(A1,CHAR(10),"@@",3))+1,FIND("@@",SUBSTITUTE(A1,CHAR(10),"@@",4))-FIND("@@",SUBSTITUTE(A1,CHAR(10),"@@",3))-1)
C1	=MID(A1,FIND("@@",SUBSTITUTE(A1,CHAR(10),"@@",7))+1,FIND("@@",SUBSTITUTE(A1,CHAR(10),"@@",8))-FIND("@@",SUBSTITUTE(A1,CHAR(10),"@@",7))-1)
D1	=MID(A1,FIND("@@",SUBSTITUTE(A1,CHAR(10),"@@",8))+1,FIND("@@",SUBSTITUTE(A1,CHAR(10),"@@",9))-FIND("@@",SUBSTITUTE(A1,CHAR(10),"@@",8))-1)

 

[wigi]

Cross-posted at Ozgrid:
http://www.ozgrid.com/forum/showthread.php?t=159109

Sorry for the effort spent, drsarao. The topic was also at Ozgrid.

 

[Data_Analyst]

Thank you for your great efforts on this drsarao. It is always great to see things in multiple methods.

wigi, I make sure that once I receive the help, I do make it a point to share the information across the many support platforms we have. All of the forums are excellent, and by seeing multiple ways of how things get done, it helps everyone become even better at helping each other.

Thank you all for your help. I greatly appreciate it!

 

[wigi]

wigi, I make sure that once I receive the help, I do make it a point to share the information across the many support platforms we have.

Please do not lie.

- You did not put any link to the other forum(s), not here, not at Ozgrid.

- There were almost 20 replies in the other topic at Ozgrid this morning, and still you refused to update the topic here.

- What about the other topic you were cross-posting: also signalling updates on the other board?

Wigi

 

[Data_Analyst]

Please be assured once everything is updated and I am satisfied with the responses, I will be updating all posts.

Thank you for looking out for the credibility of these wonderful forums.

Kind Regards.

 

[wigi]

Please be assured once everything is updated and I am satisfied with the responses, I will be updating all posts.

That's much too late.

It's all about losing time on topics that are already answered. The most basic requirement is to add a link to the other forum. Second, add updates once you get a satisfactory solution. You decided not to do so.

Turn the situation around: you are the helper spending time during the weekend on a topic that is already solved... Very nice.

You should definitely read this page: http://www.excelguru.ca/node/7

 

[Data_Analyst]

Thank you wigi. I will make sure to point out the cross posts in my future posts.

Thank you all for the help! It is truly appreciated!

 

[wigi]

Thank you.

 

[drsarao]

Thank you wigi for looking out.