How do i extract specific data into another cell?

[amir988]

i have this type of data in excel, in cell a4 i have text 34, in b4 i have text 21, and in c4 i have 55 (a4+b4) comma seprated values etc 32,34,54,543,567 and i want in cell d4 first 34 values (as an a4 written) and on e4 remaining 21 values (as per b4 written)


Here is the sample

Available = i have the data
Required= i want this data using formula ...
(see img here http: imgur.com/5o6XbcK)

 

[Peter_SSs]

Welcome to the MrExcel board!

Try
D4: =SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(C4,",",REPT(" ",200)),6800))," ",",")
E4: =SUBSTITUTE(C4,D4&",","",1)

 

[Jon von der Heyden]

D4: =trim(left(substitute(c4,",",rept(" ",255),a4),255))
e4: =substitute(c4,d4&",","")

 

[Peter_SSs]

On re-reading after Jon's post I see I didn't understand about using the value in A4. In any case, Jon has a simpler formula for D4.

But possibly also this for D4:

=LEFT(C4,FIND("|",SUBSTITUTE(C4,",","|",A4))-1)

 

[amir988]

D4: =trim(left(substitute(c4,",",rept(" ",255),a4),255))
e4: =substitute(c4,d4&",","")

thankyou john it really work, but there is small problem.

if a4 contain value 0 or empty cell, and b4 contain 55, then this formula doesn't work. (it shows #value in d4 and e4) please can u tell me what to do? thank

 

[Jon von der Heyden]

D4: =if(a4,trim(left(substitute(c4,",",rept(" ",255),a4),255)),"")
e4: =if(b4,substitute(c4,d4&if(a4,",",""),""),"")

 

[amir988]

D4: =if(a4,trim(left(substitute(c4,",",rept(" ",255),a4),255)),"")
e4: =if(b4,substitute(c4,d4&if(a4,",",""),""),"")

thank you so much, its work like charm .....