help required how to split cell

[sandeep kelkar]

split the
6 Unit Fodder Grass Bijankur-BB2 50 gm Rs. 370 2 Unit Fodder Grass Alamdar 51 1 kg Rs. 685 into two rows
result should be
6 Unit Fodder Grass Bijankur-BB2 50 gm Rs. 370
2 Unit Fodder Grass Alamdar 51 1 kg Rs. 685

 

[INCOMETAX111]

USE > DATA> TEXT TO COLUMNS

 

[sandeep kelkar]

text to columns will split entire text in different columns I require text to row this 6 Unit Fodder Grass Bijankur-BB2 50 gm Rs. 370 in 1 row & 2 Unit Fodder Grass Alamdar 51 1 kg Rs. 685 in second row or the next row

 

[bosco_yip]

In B2, formula copied down :

=TRIM(MID(SUBSTITUTE("\"&REPLACE(A$2,FIND(" ",A$2,FIND("Rs",A$2)+4),1,"\"),"\",REPT(" ",99)),ROW(A1)*99,99))

 

[pecoflyer]

Perhaps provide some more examples on a small sheet to check the syntax?

 

[sandeep kelkar]

thank you i will check & get back if any thing more is required

 

[sandeep kelkar]

below is the sample file one with yellow are to be considered as that is split into two rows without disturbing the next row as it contains other order also

 

[sandeep kelkar]

still not received any response as i have shared data

 

[shrivallabha]

This will be possible using VBA macro as this will require inserting rows.

 

[bosco_yip]

below is the sample file one with yellow are to be considered as that is split into two rows without disturbing the next row as it contains other order also

The OP attachment consist of a lot of single value and multiple value data and carried with duplicate.

Here is a formula to split the multiple value data into separate cells and include removing all duplicate.

In C1, formula copied down :

=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(CHAR(10),,$A$1:$A$234),CHAR(10),"</b><b>")&"</b></a>","//b[not(preceding::*=.)]["&ROW(A1)&"]"),"")

 

[sandeep kelkar]

thank you will get back