Split single row into multiple rows based on cell value in excel -

Split single row into multiple rows based on cell value in excel -

i have single row of info in excel. info around 5000+ values.

i want split single row multiple rows.below illustration of same.

my single row contains info follows, 1 2 3 4 5 1 2 4 5 9 5 9 2 1 4 etc...

i want single row split after every "a" value reaches. output below.

1 2 3 4 5

a 1 2 4 5 9 5 9

a 2 1 4

a etc...

can some1 help me how can done? macro fine me. have huge info 5000+ values.

regards & tia, avinash

this should job. considering info in sheet1 , output generated in worksheet sheet2.

sub psplitdata() dim rngcolloop range dim rngsheet1 range dim wkssheet2 worksheet dim intcolcounter integer dim introwcounter integer 'consider info in first row of sheet1 worksheets("sheet1") set rngsheet1 = .range(.range("a1"), .range("a1").end(xltoright)) end set wkssheet2 = worksheets("sheet2") introwcounter = 1 intcolcounter = 0 'clear previous output wkssheet2.range("a1").currentregion.clear 'loop through , create output in sheet2 rngsheet1 each rngcolloop in .columns if trim(rngcolloop) <> "" if ucase(trim(rngcolloop)) <> "a" intcolcounter = intcolcounter + 1 wkssheet2.cells(introwcounter, intcolcounter) = rngcolloop elseif ucase(trim(rngcolloop)) = "a" introwcounter = introwcounter + 1 intcolcounter = 1 wkssheet2.cells(introwcounter, intcolcounter) = rngcolloop end if end if next rngcolloop end set rngcolloop = nil set rngsheet1 = nil set wkssheet2 = nil end sub

excel excel-vba excel-formula excel-2010