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

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

 

[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