excel vba - how to split certain rows from the column dynamiclly in VBA -

i have column data has description , id code (bold char) it:

bba-34.009876

the harmful impact of typical american diet.

the number of americans recycle on regular basis.

the amount of time students use on social media.

the average college debt of undergraduate , graduate students.

how many public schools have cut arts programs.

how high school athletes succeed in college programs.

how healthy average public school lunch.

the number of low socioeconomic students fail state standardized testing.

ccv-09.89765

a beautiful summer morning in woods.

a character movie, cartoon, or book.

the frightening place on planet.

a personality resemble.

the best place winter vacation.

a holiday celebration in country.

the interesting piece of art in nearest museum.

describe favorite season.

ffg-890.786543

your first day of school.

your favorite cuisine.

the experience of parachute jumping.

the memory of people met when child.

your ideal study partner.

environmental degradation caused human activity.

r-34.896543

the worst d255 34 life.

the best place homework.

the experience of learning foreign language.

choosing college , future career.

your favorite camping spot.

the data set above example , there 50-2000 description in between id codes. question how split id codes descriptions, in separate rows in same column, , paste id codes in separate column within same row? has dynamic , not static input. id codes not have same pattern every time.

here vba code have been working , yes unifier style code within vba.

sub unifier()     dim cell range     dim x integer     dim x2 integer     dim s string     dim e string     dim e2 string     dim e3 string     dim e4 string     dim e5 string     dim e6 string     dim e7 string     dim form string          s = sheets("costos").range("l2").value 'pulls 0     x = sheets("costos").range("a" & rows.count).end(xlup).row 'task code count     x2 = sheets("costos").range("c" & rows.count).end(xlup).row 'item code     x3 = sheets("costos").range("d" & rows.count).end(xlup).row 'item description count     x4 = sheets("costos").range("g" & rows.count).end(xlup).row 'um count     x5 = sheets("costos").range("f" & rows.count).end(xlup).row 'unit rate count     x6 = sheets("costos").range("e" & rows.count).end(xlup).row 'quantity count     x7 = sheets("costos").range("h" & rows.count).end(xlup).row 'cbs count     e = sheets("costos").range("l2").value 'approved     e2 = sheets("costos").range("l3").value 'estimate type     e3 = sheets("costos").range("l4").value 'estimate number     e4 = sheets("costos").range("l5").value 'estimator     e5 = sheets("costos").range("l6").value 'approval date     e6 = sheets("costos").range("l7").value 'project number     e7 = sheets("costos").range("l8").value 'effective date     e8 = sheets("costos").range("v4").value 'task code prefix      **'id code     sheets("costos").range("d2:d" & x).select     set cell = sheets("costos").range("a2:a" & x)     each cell in cell           if cell.value = " "              cell.offset(0, 3).value = range("d2:d" & x)           end if           next**     'task code, prefix & h or d logic     sheets("costos").range("a2:a" & x).copy     sheets("est template").range("c3").pastespecial xlpastevalues     'h or d     each cell in sheets("est template").range("c3:c" & x)         if cell.value = ""             cell.offset(0, -2).value = "h"         else             cell.offset(0, -2).value = "d"         end if     next     sheets("costos").range("m3").select 'filldown task prefix     selection.autofill destination:=range("m3:m" & x7), type:=xlfilldefault     each cell in sheets("est template").range("m3:q" & x7)     next     sheets("costos").range("n2").select 'filldown milepost prefix     selection.autofill destination:=range("n2:n" & x7), type:=xlfilldefault     each cell in sheets("est template").range("n2:q" & x7)     next     sheets("costos").range("o2").select 'filldown milepost number     selection.autofill destination:=range("o2:o" & x7), type:=xlfilldefault     each cell in sheets("est template").range("o2:o" & x7)     next     'cbs & approval date     sheets("costos").range("q3").select 'removes end of cbs code     selection.autofill destination:=range("q3:q" & x7), type:=xlfilldefault     sheets("costos").range("t3").select 'fills down vlookup     selection.autofill destination:=range("t3:t" & x7), type:=xlfilldefault     sheets("costos").range("t2:t" & x).copy     sheets("est template").range("n3").pastespecial xlpastevalues     each cell in sheets("est template").range("n3:n" & x)         if cell.text = ""             cell.value = e5         end if         next     'item code     sheets("costos").range("c2:c" & x).copy     sheets("est template").range("g3").pastespecial xlpastevalues     each cell in sheets("est template").range("g3:g" & x2)      next      'estimate number     set cell = sheets("est template").range("a3:a" & x)     each cell in cell         if cell.value <> "d"              cell.offset(0, 6).value = e3         end if     next     'effective date     set cell = sheets("est template").range("a3:a" & x)     each cell in cell         if cell.value <> "d"             cell.offset(0, 8).value = e7         end if     next     'task code prefix     set cell = sheets("est template").range("a3:a" & x)     each cell in cell         if cell.value <> "d"             cell.offset(0, 9).value = e8         end if     next     'quantity & estimator     sheets("costos").range("e2:e" & x).copy     sheets("est template").range("k3").pastespecial xlpastevalues     each cell in sheets("est template").range("k3:k" & x6)         if cell.value = ""             cell.value = e4         end if     next     'unit of measure & approved     sheets("costos").range("g2:g" & x4).copy     sheets("est template").range("m3").pastespecial xlpastevalues     each cell in sheets("est template").range("m3:m" & x4)         if cell.value = ""             cell.value = e         end if     next     'unit cost & estimate type     sheets("costos").range("f2:f" & x5).copy     sheets("est template").range("l3").pastespecial xlpastevalues     each cell in sheets("est template").range("l3:l" & x5)         if cell.value = ""             cell.value = e2         end if     next     'project code     each cell in sheets("est template").range("c3:c" & x)         if cell.value = ""             cell.offset(0, -1).value = e6         else             cell.offset(0, -1).value = "estimate details"         end if     next     'source     each cell in sheets("est template").range("c3:c" & x)         if cell.value = ""             cell.offset(0, 2).value = "raildocs"         else             cell.offset(0, 2).value = ""         end if     next     'item description     sheets("costos").range("d3:d" & x3).copy     sheets("est template").range("i4").pastespecial xlpastevalues     each cell in sheets("est template").range("i4:i" & x3)      next     'short description & task prefix     sheets("est template").select     sheets("est template").range("j4").select     activecell         .formula = "=left(i4,50)"     end     selection.autofill destination:=range("j4:j" & x3), type:=xlfilldefault     each cell in sheets("est template").range("j4:j" & x3)     next     cutcopymode = false     end sub 

the bold portion haveing trouble , else working.

you can use operator test pattern

sub testlikepattern()     dim x long      x = 1 range("a" & rows.count).end(xlup).row         if cells(x, 1) "*[a-z][-]#*[.]#*" cells(x, 2) = true     next  end sub