Solved

VBA Solution For Extracting Data From A Spreadsheet

Posted on 2013-12-29
2
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 30

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 47

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

705 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