manipulating data in excel

[korolenkos]

Hi, 
I have a csv file where in column A there are IDs, and in column B there are associated data. The data is separated with a semicolon (;). And the hardest bit is if one data point shares the same category with another data, then their category name precedes it with a colon (:) between them. So something like this:

column A         Column B

thisisID             abc;cat1:xyz;cat1:yyy,cat1:zzz;cat2:ddd,cat2:eee

I want to transform this format to another format where,

Column A (ID)        Column B (abc)          Column C (cat1)        Column D (cat2)

thisisID                    abc                             xyz|yyy|zzz                ddd|eee

Is there any formula to change the format to the new format? Maybe this is something that a formula cannot solve. Any advise/help is appreciated. 

[#2]

How many rows of data do you have now, and what is the anticipated number of rows of data will you have in the future and will you be summarizing the data now or in the future by category and their associated values now or in the future

[#3]

select comun B and use the text to columns function (using ; as the delimiter), which should separate it out into separate columns. Then, if you still need to change : with | (or anything else), select those columns and use the 'find and replace' function. hope this helps.

[#4]

.....don't forget to use 'save as' and change document type to an excel format, rather than .csv