Link to home
Create AccountLog in
Avatar of Computer Guy
Computer Guy

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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