manipulating data in excel

Question

Hi,&nbsp;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&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Column BthisisID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;abc;cat1:xyz;cat1:yyy,cat1:zzz;cat2:ddd,cat2:eee&nbsp;I want to transform this format to another format where,Column A (ID)&nbsp; &nbsp; &nbsp; &nbsp; Column B (abc)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Column C (cat1)&nbsp; &nbsp; &nbsp; &nbsp; Column D (cat2)thisisID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; abc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;xyz|yyy|zzz&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ddd|eee&nbsp;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.&nbsp;&nbsp;

yea_so · Answer

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

charla74 · Answer

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.

charla74 · Answer

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

Forum Discussion

manipulating data in excel

3 Replies

Resources