Solved

VBA Solution For Extracting Data From A Spreadsheet

Posted on 2013-12-29
2
268 Views
Last Modified: 2013-12-30
Hi,

I have a spreadsheet attached that has multiple lines in Column A (this is just a sample as there are several cells continuing down Column A)

Each cell does have this data:

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

I want to get the Name and Email from each cell and put the name in Column B and email in Column C
Like-This.xlsx
0
Comment
Question by:Computer Guy
2 Comments
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39744809
Is this what you want ? I copied and modified some of your cells downward just to make sure test is ok. Make sure macros are activated and check out the results. Press on the button in sheet1

Let me know
gowflow
Like-This.xlsm
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39744816
If gowflow's solution is essentially the same as this macro then please give him all the credit.

Sub GetData()

Dim lngRow As Long
Dim intPosStart As Integer
Dim intPosEnd As Integer

For lngRow = 1 To ActiveSheet.UsedRange.Rows.Count
    intPosStart = InStr(1, Cells(lngRow, 1).Value, "Name:")
    intPosStart = intPosStart + 6
    intPosEnd = InStr(intPosStart, Cells(lngRow, 1).Value, "Preferred")
    Cells(lngRow, 2).Value = Mid(Cells(lngRow, 1).Value, intPosStart, intPosEnd - intPosStart)
    
    intPosStart = InStr(1, Cells(lngRow, 1).Value, "Email:")
    intPosStart = intPosStart + 7
    intPosEnd = InStr(intPosStart, Cells(lngRow, 1).Value, "Phone")
    Cells(lngRow, 3).Value = Mid(Cells(lngRow, 1).Value, intPosStart, intPosEnd - intPosStart)

Next
End Sub

Open in new window

0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

808 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