Forum Discussion

johannsomosa's avatar
johannsomosa
Copper Contributor
Dec 16, 2024

Store a range of numbers in one cell? Excel Question

Hey Microsoft community,

I'm working on a project and I'm wondering if this task I'm requesting for Excel to do is possible. If so, is it suitable I continue with Excel or with a different program?

Task: I'm searching for the best solution to store a range of numbers in one to two cells in Excel. (E.g. Numbers 1-10)

Why? The objective for storing a range of numbers in one cell is for the purpose of having the program pull up a record when given a number within that range. So to record the range of numbers in a compact manner, without having to record individual numbers across different columns or rows. Example is: If there are several cities that has a temperature zone of 35-40 degrees, and I want cities with temperature zones within 39-45 degrees, I want Excel to list the cities that contain the temperature temperature zones, including the cities that exist within that range. Although, the cities may not have the same minimum or maximum temperature range, 2-3 degrees of temperature exist within the range. 

I've attempted this on my own but haven't been able successful in having excel store a range of numbers in one cell without it being separated by commas. The manner of recording numbers separated by commas in one cell, results in Excel identifying that cell ias a text rather than as a range of numbers.

  • johannsomosa's avatar
    johannsomosa
    Copper Contributor

    Excuse me for the screen shot. Of course, I've attached a link to the workbook.

    [Link removed by Tech Community moderation team.]

  • m_tarler's avatar
    m_tarler
    Bronze Contributor

    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")

    • m_tarler's avatar
      m_tarler
      Bronze Contributor

      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 

       

      • johannsomosa's avatar
        johannsomosa
        Copper Contributor

        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.

    • johannsomosa's avatar
      johannsomosa
      Copper Contributor

      Hey m_tarler , thank you for your response! From what I understand about your response, is that to capture the range of, for example 1-25, I could utilize the max function. In the case scenario, where I want all crops where temperature zone < 25 degrees, max < 25, such so that all crops below 25 is returned. Is this correct?

      Attached is the link to the drive: 
      https://docs.google.com/spreadsheets/d/1shn8arKSoY9nkuGF7cDEFMiy8rnOWc4d/edit?usp=sharing&ouid=113669357501570955749&rtpof=true&sd=true

    • johannsomosa's avatar
      johannsomosa
      Copper Contributor

      Hey m_tarler, 

      Thank you for this response. From what I understand, when instructing excel to pull up different crops based on the temperature zones or hardiness zones, we utilize the max < the value. In doing so, Excel will look for every crop, with temperature, or hardiness zone, below the the value. Is this correct?

      I'll go ahead an attempt to rearrange my worksheet with MIN and MAX columns. As for the worksheet, I've attached it here. Please let me know what you think. Thank you for your time.
      https://docs.google.com/spreadsheets/d/1shn8arKSoY9nkuGF7cDEFMiy8rnOWc4d/edit?usp=drive_link&ouid=113669357501570955749&rtpof=true&sd=true

      • m_tarler's avatar
        m_tarler
        Bronze Contributor

        you could use the max min i suggested but that was more for something like 'I live in area with a temperature of X so find all zones that include that temperature'  

        In this case it appears you already have hardiness zone and want all crops that include that zone so my above formula should work.  I updated it based on your sheet info:

        =TEXTJOIN(", ",,FILTER( USDA_Crops5[Crop], ISNUMBER(SEARCH([@[Hrdiness Zone 2023]]&",", USDA_Crops5[Hardiness Zones]&",")), "None"))

        But note the list of crops is large so those cells got big.  i hope this is what you are looking for.

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor

    https://filetransfer.io/

    Please share a sample file and expected result.

     

    • johannsomosa's avatar
      johannsomosa
      Copper Contributor

      Hey peiyezhu

      Thank you for your response. I commented details regarding the problem in a response. If you're interested, I've also attached the file: https://docs.google.com/spreadsheets/d/1shn8arKSoY9nkuGF7cDEFMiy8rnOWc4d/edit?usp=sharing&ouid=113669357501570955749&rtpof=true&sd=true

  • Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

    • johannsomosa's avatar
      johannsomosa
      Copper Contributor

      "Sheet 1" lists Hardiness Zone, Crop, Annual or Perennial, Min. Temperature Range, Max Temperature Range. My goal is to pull up all the crops that exist within the Hardiness Zones. (Ex. If searching Hardiness Zone 9, every Crop with the Hardiness Zone of 9 is pulled up.) 

      I'm trying to figure out how store data in the manner that if I wanted every crop with Hardiness Zone of 9, it would pull all the crops. Originally, my idea was to store the hardiness zones as a range. However, I haven't found a solution towards that.

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        I'm too lazy to recreate that. Please attach a small sample workbook (not screenshots) demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar.

Resources