VBA Solution For Extracting Data From A Spreadsheet

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

Microsoft ExcelVB Script

Last Comment

Martin Liss

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

gowflow

12/29/2013

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Martin Liss

12/29/2013

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Certified Expert Program

Credly Partnership

Udemy Partnership