Import CSV Problem with single row multiple records for each entry

[SOLTEC]

I have a huge csv file when I import it into Excel I have discovered inaccuracies in the data integrity. The name, address, city, State and Zip Code are followed by another Name, Address, City, State and Zip code. I need a formula of VBA to search for strings beyond the zip code to move them to a new row.

John Q. Smith, 123 Main Street, Anytown, State Zip CodeJane Jones, 132 Bart Street, Everytown, State Zip Code

I want to find Jane Jones and move her to a new row into the correct column field, or place a , to delimit that into a new record row

Dowdle Funeral Home Millport	270 MCADAMS ST P O Box 218	Millport	AL 35576
Dryden Funeral Home	1467 Almon Street	Heflin	AL 36264Dunklin & Daniels Funeral Home	63 Camden Bypass	Camden	AL 33726
Dunklin & Daniels Funeral Home Greenville	812 W Commerce Street P. O. Box 573	Greenville	AL 36037
Dunklin and Daniels Funeral Home	812 West Commerce Street	Greenville	AL 36307
E. G. Cummings Memorial Funeral Home	Inc.	1120 Bragg Street	Montgomery	AL 36108
Earnest Johnson Funeral Home	8531 Zion City Rd	Birmingham	AL 35206
Eastside Funeral Home Ensley	827 19Th St	Birmingham	AL 35218
Eastside Funeral Home Llc	5523 1st Avenue North	Birmingham	AL 35212
Elkins East Chapel Killen	7435 Hwy 72	Killen	AL 35645
Elkins East Chapel Killen	7435 Highway 72	Killen	AL 35645
Elkins Funeral Home Florence	1535 Hermitage Drive	Florence	AL 35630Elkins Funeral Home Florence	1535 Hermitage Dr.	Florence	AL 35630
Elliott Brown Service Funeral Home Moulton	15215 Court Street	Moulton	AL 35650
Ellison Memorial Funeral Home Clanton	1709 Lay Dam Road P.O. Box 2716	Clanton	AL 35045
Elmwood Cemetery & Mausoleum	600 Martin Luther King Jr Dr	Birmingham	AL 35211
Ervin Funeral Chapel	1518 Brown Avenue	Anniston	AL 36201
Etowah Memorial Chapel	12600 U.S. Highway 431 South	Sardis City	AL 35956
Evans Funeral Home Florala	1972 6th St.	Florala	AL 36442
Faith Chapel Funeral Home	700 23Rd St E	Jasper	AL 35501
Faith Memorial Chapel Birmingham	1037 3rd Avenue West	Birmingham	AL 35204
Faith Memorial Chapel Funeral Services	600 9Th Ave N	Bessemer	AL 35020Faith Memorial Chapel Funeral Services	600 9th Ave North	Bessemer	AL 35020

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Peter_SSs]

Give this a try in a copy of your workbook. Note that it will not resolve issues like you appear to have in the 5th row of your sample data where the column A value appears to have been split across columns A:B and the other data in that row pushed across one column.

Sub Insert_Rows()
  Dim r As Long
  
  Application.ScreenUpdating = False
  For r = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Cells(r, "D").Value Like "[A-Z][A-Z] #####?*" Then
      Rows(r + 1).Insert
      Rows(r + 1).Resize(, 4).Value = Cells(r, "D").Resize(, 4).Value
      Cells(r + 1, "A").Value = Mid(Cells(r + 1, "A").Value, 9)
      Cells(r, "D").Value = Left(Cells(r, "D").Value, 8)
      Cells(r, "E").Resize(, 3).ClearContents
    End If
  Next r
  Application.ScreenUpdating = True
End Sub