asked on

Complex data extraction

Hello, so I am trying to find a way to extract the City to Cell E1 and State in F1, and zip code in G1 any idea of how I could get this data extracted. The only thing I can think of that will be constant is "(the" the address, city, and state will be separated by commas. Any idea.

That company LLC, an Ohio limited liability company, whose address is 1111 W. 140th St., Cleveland, Ohio 44111 (the "Zone"), for the consideration of Ninety Six Thousand U.S. Dollars and 00/100 cents ($96,000.00) received to Borrower's full satisfaction of First Name,

Martin Liss

This assumes the data is in A1.

Sub ExtractAddress()
Dim strParts() As String

strParts = Split(Range("A1"), ",")
Range("E1") = strParts(3)
Range("F1") = Split(Trim(strParts(4)), " ")(0)
Range("G1") = Split(Trim(strParts(4)), " ")(1)

End Sub

Open in new window

sandramac

ASKER

I had to edit the macro, however, the zip code is not pulling into G1

Sub ExtractAddress()
Dim strParts() As String

strParts = Split(Range("A1"), ",")
Range("E1") = strParts(4)
Range("F1") = Split(Trim(strParts(5)), " ")(0)
Range("G1") = Split(Trim(strParts(6)), " ")(1)

End Sub

ASKER CERTIFIED SOLUTION

Martin Liss

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

sandramac

ASKER

Thank you so much

Martin Liss

Here's a solution that will handle 2 or more spaces between the city and zip.

Sub ExtractAddress()
Dim strParts() As String
Dim strZip As String

strParts = Split(Range("A1"), ",")
Range("E1") = strParts(3)
Range("F1") = Split(Trim(strParts(4)), " ")(0)

strZip = strParts(4)

Do
    strParts(4) = Replace(strParts(4), "  ", " ")
    If strParts(4) = strZip Then
        Exit Do
    End If
    strZip = strParts(4)
Loop

Range("G1") = Split(Trim(strZip), " ")(1)

End Sub

Open in new window

Martin Liss

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
       Experts Exchange Most Valuable Expert (MVE) 2015, 2017
       Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
       Experts Exchange Top Expert VBA 2018
       Experts Exchange Distinguished Expert in Excel 2018

sandramac

ASKER

Thank you, yeah I have this long paragraph in cell A1, trying to extract certain values out of it.