The follow formula will work in excel 2007 and 2010 assuming the countries cell is A1 and that the values cell is B1 and that both cells contain 7 values separated by 6 commas:
Countries:
=LEFT(A1,SEARCH(",",A1)-1)&","&LEFT(RIGHT(A1,LEN(A1)-SEARCH(",",A1,SEARCH(",",A1)+1)),SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1)+1)+1)-SEARCH(",",A1,SEARCH(",",A1)+1)-1)&","&LEFT(RIGHT(A1,LEN(A1)-SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1)+1)+1)),SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1)+1)+1)+1)-SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1)+1)+1)-1)&","&RIGHT(A1,LEN(A1)-SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1)+1)+1)+1)+1)+1))
Values:
=LEFT(B1,SEARCH(",",B1)-1)+LEFT(RIGHT(B1,LEN(B1)-SEARCH(",",B1)),SEARCH(",",B1,SEARCH(",",B1)+1)-SEARCH(",",B1)-1)&","&LEFT(RIGHT(B1,LEN(B1)-SEARCH(",",B1,SEARCH(",",B1)+1)),SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)-SEARCH(",",B1,SEARCH(",",B1)+1)-1)&","&LEFT(RIGHT(B1,LEN(B1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)),SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)-1)+LEFT(RIGHT(B1,LEN(B1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)),SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)+1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)-1)+LEFT(RIGHT(B1,LEN(B1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)+1)),SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)+1)+1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)+1)-1)&"," &RIGHT(B1,LEN(B1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)+1)+1))
This could also be done with VBA.
Let me know if you're using an older version of excel and I can post that.
When you say comma separated cells, are the actually in different cells, or are then in the same cell> – soandos – 2012-05-22T19:00:29.963
the countries are in one cell, and the values are in another cell. – Hai Myster – 2012-05-22T19:12:01.627