Solved

Find specific data and put in in a another cell.

Posted on 2013-12-29
14
286 Views
Last Modified: 2013-12-29
Hi,

I have some several multiple line cells in column A on my worksheet with a lot of unwanted data. All cells contain an email address and a name which I want to put in separate columns.

If the cell contains "Name:" and "Email:", I want to put the Name in Column B and Email in Column C

Name: John Doe
Email: 123@123.net <mailto:123@123.net>

Any ideas?
0
Comment
Question by:Computer Guy
  • 7
  • 6
14 Comments
 
LVL 9

Expert Comment

by:guswebb
ID: 39744610
Can you post an example of exactly how the raw data looks in column A and I can give you a solution?
0
 
LVL 3

Author Comment

by:Computer Guy
ID: 39744615
<https://www.domain.com/images/blank.gif>        <http://u525751.domain.org/wf/click?upn=2PDGsJdrvMFn4HYhujD9APwcsO48OP2i1Eg-2FBzaMqic-3D_DddzbN31GVUhjhGwbEvXEMP5dJei3mlOToh-2FN41KD-2BMLrfZanG6efdP-2FNRGWg2dEKxglbqFzu4-2FWBUb8ZgP0Zziz5ionD4BXnJcw0e1JXh9zCJiNXY7-2BGHmlWhklGZLKBSP2wxYxGYK1ejcQml-2BXkt7rspJx-2Be5HRhOevFl2Q6bl0q3MYXI2XkO5fBliwA21YgIph05ur533Uay9nTMqTw-3D-3D>        
To have your ad appear: contact us <http://u525751.domain.org/wf/click?upn=2PDGsJdrvMFn4HYhujD9ANgVi1zXdXDuUaYHN4aJPFKXhppx67ryLLNFMtyvDJ3Z_DddzbN31GVUhjhGwbEvXEMP5dJei3mlOToh-2FN41KD-2BMLrfZanG6efdP-2FNRGWg2dEKxglbqFzu4-2FWBUb8ZgP0ZxC0kfCRluaaEb7VqWcoAYQMlj8NBVR1sGb8aIEQWiHkgCL0OzMK0IPY0h2zRVRHTo1fpd4BepIcIZR2AtfSMabukRkijAnnSk4wO9CruAkbmhmVhEhguetnaxf1h9RI3A-3D-3D>        
 <https://www.domain.com/images/blank.gif>       
 <https://www.domain.com/images/blank.gif>       
 <https://www.domain.com/images/blank.gif>       
 <https://www.domain.com/images/blank.gif>              <https://www.domain.com/images/blank.gif>       


Hello,

      
New Job Alert from domain.com.

Type of Event and Location
Event Type: Wedding
State: AK
City: CAPITOL Just 94 miles away. (View Map)


Event Date
Date: 05/24/2014 , Saturday
Start Time: TBD
End Time: TBD


Attendence: 0


Details:


Thanks for being a Platinum Member with us!




Primary Contact Information
Name: Jane Doe
Preferred Method of Contact:
Email: test@domain.com
Phone: (212)555-1212
Best Time to Contact: Any Time
Cell Phone:
Best Time to Contact: Any Time
Address:
,
Budget : $400.00
Best way to contact me? :
How soon to make a decision? :
Indoor or outdoor event? : indoors without stairs
0
 
LVL 9

Expert Comment

by:guswebb
ID: 39744616
Think you need to try that again!
0
 
LVL 3

Author Comment

by:Computer Guy
ID: 39744618
That is the data inside 1 cell,

I want just the email and name extracted into separate cells
0
 
LVL 9

Expert Comment

by:guswebb
ID: 39744620
You haven't given me an example of *exactly how that data looks*.

It could be, Name:Bill Smith Email:bill@yahoo.com

OR  it might look like...

Name: Bill Smith, Email: bill@yahoo.com

OR some other variation.

Anyhow...see the attached spreadsheet, which assumes how the data will look in column A. Let me know if you need this adapting to suit the format of the raw data.
email-.xlsx
0
 
LVL 3

Author Comment

by:Computer Guy
ID: 39744623
Hi, sorry about that. Please check this out as this is how I want it to look like.
Like-This.xlsx
0
 
LVL 9

Expert Comment

by:guswebb
ID: 39744628
Thanks. To be clear....will the raw data always adopt this format, and in particular contain the lines 'Primary Contact Information', 'Preferred Method of Contact:' and 'Phone:'?

Primary Contact Information
Name: Jane Doe
Preferred Method of Contact:
Email: test@domain.com
Phone: (212)555-1212
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 3

Author Comment

by:Computer Guy
ID: 39744630
yes 100% of the time!
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39744636
For name use

=MID(A1,FIND("Name:",A1)+LEN("Name:")+1,FIND(CHAR(13),A1,FIND("Name:",A1))-FIND("Name:",A1)-LEN("Name:"))


for Email replace Name with Email using correct spelling and case

=MID(A1,FIND("Email:",A1)+LEN("Email:")+1,FIND(CHAR(13),A1,FIND("Email:",A1))-FIND("Email:",A1)-LEN("Email:"))

Do the same for other fields.
0
 
LVL 9

Accepted Solution

by:
guswebb earned 500 total points
ID: 39744638
Ok, use the attached spreadsheet. Sheet 1 contains the raw method, Sheet 2 contains the condensed format for you.
Like-Thisv2.xlsx
0
 
LVL 3

Author Comment

by:Computer Guy
ID: 39744648
Some of them say #VALUE! in them
0
 
LVL 9

Expert Comment

by:guswebb
ID: 39744651
Upload your file with those values in.
0
 
LVL 3

Author Comment

by:Computer Guy
ID: 39744736
I was actually thinking something in VBA to search then put the values in the other cells
0
 
LVL 9

Expert Comment

by:guswebb
ID: 39744767
That wasn't explicit in your original request, and since you asked for "any ideas" I'd have thought using Excel's native formulae would also meet the objective! Can you upload the file which is throwing errors and I shall see what I can do for you?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sum Based On Criteriya 7 19
Display the VBA Userform in Screen Center 4 31
Excel Formula 4 28
NEED TO UPDATE DATA IN EXCEL 18 29
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now