Computer Guy
asked on
Find specific data and put in in a another cell.
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?
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?
guswebb
Can you post an example of exactly how the raw data looks in column A and I can give you a solution?
ASKER
<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
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
Think you need to try that again!
ASKER
That is the data inside 1 cell,
I want just the email and name extracted into separate cells
I want just the email and name extracted into separate cells
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
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
ASKER
Hi, sorry about that. Please check this out as this is how I want it to look like.
Like-This.xlsx
Like-This.xlsx
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
Primary Contact Information
Name: Jane Doe
Preferred Method of Contact:
Email: test@domain.com
Phone: (212)555-1212
ASKER
yes 100% of the time!
For name use
=MID(A1,FIND("Name:",A1)+L
for Email replace Name with Email using correct spelling and case
=MID(A1,FIND("Email:",A1)+
Do the same for other fields.
=MID(A1,FIND("Name:",A1)+L
for Email replace Name with Email using correct spelling and case
=MID(A1,FIND("Email:",A1)+
Do the same for other fields.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Some of them say #VALUE! in them
Upload your file with those values in.
ASKER
I was actually thinking something in VBA to search then put the values in the other cells
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?