excel vba - splitting a string text into separate rows in VBA -

i have 2 text boxes in excel (or csv file) below: text box 1 contains (#11111,#22222,#33333), text box 2 contains (#55555)

#11111,#22222,#33333  #55555 

i want text between , on 3 different rows , repeat text in 2nd text box looks below:

#11111   #55555 #22222   #55555 #33333   #55555 

i new vba. reading string functions can't come logic on how it.

any appreciated.

hi @tim williams - advice. did manage write short code accomplishes task overwrites text if have in 2nd row , 3rd row.

 sub splitcells()  dim txt string  dim txt2 string   dim integer  dim cell1 variant   txt = range("a1", "a1").value  cell1 = split(txt, ",")  = 0 ubound(cell1)  cells(i + 1, 1).value = cell1(i)  next   txt2 = range("b1", "b1")  = 1 ubound(cell1)  cells(i + 1, 2).value = txt2  next   end sub 

any advice on how push data on row 2 downwards .....

i not know how give hint adjust macro have coded think after.

you talk overwriting data in 2nd or 3rd row assume have several rows containing data in format. have therefore converted code loop works down column until finds blank row.

i avoid overwriting data below current row inserting rows necessary.

i have changed code in ways believe makes code more maintainable. have explained reasons these changes.

i have not explained new statements have used. easy statement once know exists ask questions if unclear.

i hope helps.

option explicit sub splitcells()    ' * vba, integer declares 16-bit value while long declares 32-bit   '   value. 16-bit values require special processing , slower.   '   long preferred.   ' * not variable names such i.  not matter   '   tiny macro larger macro does.  not matter   '   matters when return macro in 6 or 12 months amend   '   it.  want able @ variables , know   '   are.  have named variables according system.  not   '   asking system have system.  can return   '   macros wrote years ago , recognise variables.   dim inxsplit long   ' dim integer    ' * split returns string array.  variant can hold string array   '   access slower.  variants can useful use when   '   need flexibility offer.   dim splitcell() string   ' dim cell1 variant    dim rowcrnt long    ' * "range" operates on active worksheet.  relying on correct   '   worksheet being active when macro called.  also, when return   '   macro in 6 or 12 months remember worksheet   '   supposed active.  ".range" operates on worksheet specified in   '   statement.  doe not matter worksheet active ,   '   absolutely clear worksheet target of code.   worksheets("sheet1")      rowcrnt = 1         ' first row containing data.      while true        ' * use .cells(row, column) rather .range because more       '   convenient when need change row and/or column numbers.       ' * note column value can number or column identifier.       '   = 1, b=2, z=26, aa = 27, etc.  not doing arithmetic       '   columns have used "a" , "b" find more       '   meaningful 1 , 2.       if .cells(rowcrnt, "a").value = ""         exit       end if        splitcell = split(.cells(rowcrnt, "a").value, ",")        if ubound(splitcell) > 0         ' cell contained comma row spread across         ' 2 or more rows.         ' update current row         .cells(rowcrnt, "a").value = splitcell(0)          ' each subsequent element of split value, insert row         ' , place appropriate values within it.         inxsplit = 1 ubound(splitcell)           rowcrnt = rowcrnt + 1           ' push rest of worksheet down           .rows(rowcrnt).entirerow.insert           ' select appropriate part of original cell row           .cells(rowcrnt, "a").value = splitcell(inxsplit)           ' copy value column b previous row           .cells(rowcrnt, "b").value = .cells(rowcrnt - 1, "b").value         next       end if        rowcrnt = rowcrnt + 1      loop    end   end sub