ms office - How do I split a row of text into different columns according to number of characters using a macro in Microsoft Excel? -

i want know if can use macro in excel separate data in single column different colums according number of characters. example, have in column a

a

ab

abc

1a

564

8

what need this, in colums a, b , c

a ab abc

8 1a 564

thanks.

use following formula in new column b next column a:

=iferror(index($a$1:$a$6,small(if(len($a$1:$a$6)=1,row($a$1:$a$6),99999),row()),1),"")   

array formula press ctrl+shift+enter @ same time

and drag down, write values of b length 1, , when gives empty means no more values length 1
small find cell length 1 (row()=1, 1st cell length=1, row()=2, 2nd cell length =1 ...)
if return rows corresponding condition
index return cell
iferror return empty "" if no more match
second column write 2 instead of 1 in len($a$1:$a$6)=2

=iferror(index($a$1:$a$6,small(if(len($a$1:$a$6)=2,row($a$1:$a$6),99999),row()),1),"")   

for third column write 3 in len($a$1:$a$6)=3

=iferror(index($a$1:$a$6,small(if(len($a$1:$a$6)=3,row($a$1:$a$6),99999),row()),1),"")