One cell multiple values, data analysis

Question

Hello,

As a complete outsider to excel I need to analyze data based on different categories and keywords. As some keywords fell into the same category I put them in one cell hoping the # will make them read by the system as one distinct word and not the entire cell so it could still be analyzed. However having done my analysis I know realized that it is not possible and the whole cell will be presented as one. An example category: colors

cell 1: #red #blue; cell 2:#blue #red, and this is already considered two different values and all the red and blues aren't combined together. What should I do so every keyword would be considered separately?

Thank you all for help!

Answer 1

Hi

I'm AnnaThomas and I'd happily help you with your question. In this Forum, we are Microsoft consumers just like yourself.

You can use the Text to Columns feature in Excel to split the values into separate cells. This will allow you to analyze each value separately. Here’s how:

Select the column that contains the cells with multiple values. Click on the Data tab in the ribbon, then click on Text to Columns in the Data Tools group. In the Convert Text to Columns Wizard, select Delimited and click Next. Select the delimiter that separates your values (in your case, it would be “Other” and enter “#” in the box next to it), then click Next. Choose the destination for the split data (by default, it will overwrite the original data), then click Finish.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Answer 2

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.