Forum Discussion
Store a range of numbers in one cell? Excel Question
As noted by the other responses more information and in particular a sample sheet would be helpful.
That said, you are correct that excel can NOT store a range of numbers in a single cell as values and putting a list or range of values into a single cell will cause it to be treated as text. That said it doesn't mean what you want to do isn't possible. There are many ways to treat text and to extract numbers from text or just search the text. Alternatively you say 1 or 2 cells and it sounds like it is a range of values (i.e. continuous) so could you just have min and max columns for the range and then return the rows that have a min < the value and the max > the value. Again this would be easier to show if we have a sample sheet. But it sounds like a FILTER() might work well for you. Here is a formula in 'pseudo code' to return the "data" of interest based on the corresponding [min] and [max] columns bracketing the "value" of interest
=FILTER( data, (data[min] < value)*(data[max] > value), "none found")
based on your images something like the following should work. I'm using "CropList" to refer to the Table in the first image and I can't read the column name for the Hardiness Zone in the second image so just added the ...
=TEXTJOIN(", ",,FILTER( CropList[Crop], ISNUMBER(SEARCH([@[Hardiness Zone ....]]&"," , CropList[Hardiness Zone]&",")), "None"))
Note I added the &"," to both the search term and the list of Hardiness Zone so that a "1" doesn't match with 10, 11, ...
I added the TEXTJOIN since in a table like that you can't spill all the results
Hey, this is awesome! Thank you so much for sending this solution over. I'm sorry you had to create this through the images. I would like to place this blame on Microsoft, it states its requiring permission before posting my comment. Will go ahead and try this, and post back the results! Again, thank you.
