excel - Split comma separated entries to new rows -
i have data in sheet
col col b 1 angry birds, gaming 2 nirvana,rock,band
what want split comma separated entries in second column , insert in new rows below:
col col b 1 angry birds 1 gaming 2 nirvana 2 rock 2 band
i sure can done vba couldn't figure out myself.
you better off using variant arrays rather cell loops - quicker code wise once data sets meaningful. thoug code longer :)
this sample below dumps column c , d can see orginal data. change [c1].resize(lngcnt, 2).value2 = application.transpose(y)
[a1].resize(lngcnt, 2).value2 = application.transpose(y)
dump on original data
[updated regexp remove blanks after ,
ie ", band" becomes "band"]
sub slicendice() dim objregex object dim x dim y dim lngrow long dim lngcnt long dim temparr() string dim strarr set objregex = createobject("vbscript.regexp") objregex.pattern = "^\s+(.+?)$" 'define range analysed x = range([a1], cells(rows.count, "b").end(xlup)).value2 redim y(1 2, 1 1000) lngrow = 1 ubound(x, 1) 'split each string "," temparr = split(x(lngrow, 2), ",") each strarr in temparr lngcnt = lngcnt + 1 'add 1000 records resorted array every 1000 records if lngcnt mod 1000 = 0 redim preserve y(1 2, 1 lngcnt + 1000) y(1, lngcnt) = x(lngrow, 1) y(2, lngcnt) = objregex.replace(strarr, "$1") next next lngrow 'dump re-ordered range columns c:d [c1].resize(lngcnt, 2).value2 = application.transpose(y) end sub
Comments
Post a Comment