I am trying to take a column of values that represent grades at particular schools. The possible values are P, K, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12. I want to take the values in each cell and move them to separate columns of P, K, 1, etc. However, the values listed in the cells vary, i.e., one cell would be "6-12", but another might be "K-12", still another will have "6,8-12", another would be "K-5,7-9", etc.
I have tried manually changing the ranges into values with CTRL+H, e.g., "6-12" becomes "6,7,8,9,10,11,12". But, it is getting really tedious, especially since I will still need to perform a text-to-columns function. And, that will not be a clean process. Either way, I cannot get lookup function to deal with multiple values in one cell.
I need a lookup function (or something), that can decipher between multiple values in one cell separated by a comma, and also to be able to lookup given values that exist in numeric ranges (e.g., 8 exists in 6-12), and I need to be able to identify that a "K" exists wherever there is a range that begins with "P".
Any ideas for a clean process?
I have tried manually changing the ranges into values with CTRL+H, e.g., "6-12" becomes "6,7,8,9,10,11,12". But, it is getting really tedious, especially since I will still need to perform a text-to-columns function. And, that will not be a clean process. Either way, I cannot get lookup function to deal with multiple values in one cell.
I need a lookup function (or something), that can decipher between multiple values in one cell separated by a comma, and also to be able to lookup given values that exist in numeric ranges (e.g., 8 exists in 6-12), and I need to be able to identify that a "K" exists wherever there is a range that begins with "P".
Any ideas for a clean process?
