Searching for text string inside a cell for a column of cells using VBA

[jwright8]

Hello all, this kinda relates to my post from yesterday.

I have a sheet that's kind of a mess with tons of entries.. unfortunately, they weren't put in in a way to make them easily workable.

The sheet looks something like this:

123123
Bob Smith
123 Spring Street, Bobville, TN 12345 (999) 999-9999
Jane Doe
321 Fir Tree Lane, Janeville, FL 54321 (888) 888-8888

<tbody>
</tbody>

As I illustrated above, the numbers are really nonsensical and the placement of the text occasionally varies. However, all of the text is is the A column.

I initially had AlphaFrog help me here to determine a macro to remove all the blank lines seemingly arbitrarily placed throughout the sheet (we'll call it Sheet2). I put my button on Sheet1 as a macro for the following, where I could define the sheet name for the data sheet in cell I1.

    Dim i As Long, LastRow As Long
    Dim shtName As String
    
    shtName = Range("I1")
    
    With Sheets(shtName)
    
        LastRow = .Cells.Find("*", , , , 1, 2).Row
        
        Application.ScreenUpdating = False
        For i = LastRow To 1 Step -1
            If Application.CountA(.Rows(i)) = 0 Or .Range("A" & i).Value Like "  *" Then .Rows(i).Delete
        Next i
        Application.ScreenUpdating = True
        
    End With

And it worked well...

What I'm trying to add to the macro is to have a cell on Sheet1 (we'll say J1) where I can put the state in and another cell (K1) that I can put the sheet name for ANOTHER, blank sheet (Sheet3), and it will search for the state until the last row. Whatever entries it finds with that state, it will paste that cell and the cell immediately above it to the new sheet that I specified in K1 on Sheet1.

I'll then use LEFT(), RIGHT(), and SEARCH() functions to split the data using Excel so I can make it into a more workable format (column for address, city, state, zip, phone #) instead of having to go back and retype all of it. That doesn't really have to be in the macro, but any readme files or examples you could point me to in maybe working with those functions in VBA would be helpful, since it would be handy to have it all handled by the macro, but not super necessary. I can always just copy the formula down the column in Excel.

Sorry for being so complicated, and if you'd prefer to point me in the right direction readme/example wise instead of spoon feeding me I'm more than happy to give it a shot (which I'm currently doing with not much success). Thanks a lot!

 

[jwright8]

I have the following for splitting the cell as an Excel formula, though I don't know how different it is with VBA.

Phone #:

=RIGHT(cellnumber, 14)

Zip code:

=LEFT(RIGHT(cellnumber, 20), 5)

State:

=LEFT(RIGHT(cellnumber, 23), 2)

City:

=MID(cellnumber, SEARCH(",", cellnumber)+2, SEARCH(",", cellnumber, SEARCH(",", cellnumber)+1)-SEARCH(",", cellnumber)-2)

Address:

=LEFT(cellnumber,SEARCH(",", cellnumber)-1)