Skip to main content

I'm new to formulas but have learned quite a bit from these forums. I have arrays of Hardiness Zones for plants, which are written like this: 4a, 4b, 5a, 5b, 6a, 6b and so on. I need to put them into ranges like this "Zone 4-8". So far I have it this way:

I used this formula to get it:

IF(NOT(Zones=''), 'Zone' & ' ' & LEFT(ARRAYJOIN({Number value (from Zone test)}), 2) & '-' & RIGHT(ARRAYJOIN({Number value (from Zone test)}), 2), "")

There is another column named Zones, and in the linked table, I had to put a space after 9 so that it would still register as having 2 characters in the field instead of just one.

There are two problems with my outcome.

Problem 1: Some of them only have a single number in the Number Value field. So that return looks like "Zone 10-10" and I want it just to say "Zone 10". I'm trying to use a nested IF(LEN) function in there but it's not coming out right. So if the length of the Number Value field = 2, I want it only to return the contents of the Number Value field. If it's >2, I want it to do the formula above.

I tried this formula and am getting #ERROR returned:

IF(NOT(Zones=''), 'Zone' & ' ' & IF(LEN({Number value (from Zone test)})=2, {Number value (from Zone test)}, LEFT(ARRAYJOIN({Number value (from Zone test)}), 2) & '-' & RIGHT(ARRAYJOIN({Number value (from Zone test)}), 2)), "")

My second problem might not be solveable but I'll ask anyway. If a plant's Hardiness zone includes 5a, 5b, 6a, 6b, 7a, 7b, I want to just say "Zone 5-7." Because it includes all the subzones of 5, 6, and 7. But sometimes it only is part of a zone, so it might be Zone 5b, 6a, 6b, 7a. In which case it would still need to say "Zone 5b-7a." Is it possible for a formula to tell when all subzones are included, to just return one digit?

 

Hmm, if you create 2 lookup fields, one to display the first linked item and one to display the last one, and then use a formula field to combine them, would that work? 


Apologies, I don't know how your base is set up so this might not work, but if it does it might be a lot simpler

For the second problem, I think you could do it, but it might be a bit of a chore as it would involve checking whether the other subzones of a specific subzone exist, and then displaying the single digit if they don't exist, and then you'd have to do it for each subzone.  Another complicating factor would be whether it's possible for a subzone in the middle to not be compatible, e.g. 5a, 5b, 6a, 7a?

Hmm, if you create 2 lookup fields, one to display the first linked item and one to display the last one, and then use a formula field to combine them, would that work? 


Apologies, I don't know how your base is set up so this might not work, but if it does it might be a lot simpler

For the second problem, I think you could do it, but it might be a bit of a chore as it would involve checking whether the other subzones of a specific subzone exist, and then displaying the single digit if they don't exist, and then you'd have to do it for each subzone.  Another complicating factor would be whether it's possible for a subzone in the middle to not be compatible, e.g. 5a, 5b, 6a, 7a?


Thanks, but when I do the First or Last values, it still gives 10 and 10 for example, if 10 is the only Value in that field. I'm looking for something that would say like, If there is only one Value in the cell, don't do the Left and Right arrayjoin setup but just return that single value. That's why I tried this part of the formula, but it's not working: 

IF(NOT(Zones=''), 'Zone' & ' ' & IF(LEN({Number value (from Zone test)})=2, {Number value (from Zone test)}, LEFT(ARRAYJOIN({Number value (from Zone test)}), 2) & '-' & RIGHT(ARRAYJOIN({Number value (from Zone test)}), 2)), "")

As for the second problem, there shouldn't be any case where it's missing a subzone in the middle...of course nothing is perfect and things get missed but ideally that shouldn't happen

Hmm, if you create 2 lookup fields, one to display the first linked item and one to display the last one, and then use a formula field to combine them, would that work? 


Apologies, I don't know how your base is set up so this might not work, but if it does it might be a lot simpler

For the second problem, I think you could do it, but it might be a bit of a chore as it would involve checking whether the other subzones of a specific subzone exist, and then displaying the single digit if they don't exist, and then you'd have to do it for each subzone.  Another complicating factor would be whether it's possible for a subzone in the middle to not be compatible, e.g. 5a, 5b, 6a, 7a?


Sorry to double reply but I found the solution for my first problem! Apparently Airtable doesn't like to use LEN on a lookup field, but I found someone's comment saying to use &'' after the field name and it would solve the problem. So this new formula works: 

IF(NOT(Zones=''), 'Zone' & ' ' & IF(LEN({Number value (from Zone test)}&'')=2, {Number value (from Zone test)}, LEFT(ARRAYJOIN({Number value (from Zone test)}), 2) & '-' & RIGHT(ARRAYJOIN({Number value (from Zone test)}), 2)), "")

 

In the pic, the left column is the old formula, and the rightmost column is the new working formula with the correct returns!

Sorry to double reply but I found the solution for my first problem! Apparently Airtable doesn't like to use LEN on a lookup field, but I found someone's comment saying to use &'' after the field name and it would solve the problem. So this new formula works: 

IF(NOT(Zones=''), 'Zone' & ' ' & IF(LEN({Number value (from Zone test)}&'')=2, {Number value (from Zone test)}, LEFT(ARRAYJOIN({Number value (from Zone test)}), 2) & '-' & RIGHT(ARRAYJOIN({Number value (from Zone test)}), 2)), "")

 

In the pic, the left column is the old formula, and the rightmost column is the new working formula with the correct returns!

Ah, nice, glad you got it sorted! 

I ended up going down the rabbit hole of trying to figure this out and I put something together here that I think is what you're looking for

 

And you can view the formula after you've duplicated the base

The formula's...pretty crazy and made my head hurt a bit, but it appears to do what you want heh.  I'm populating the 'Zone Range" field with the lookup method that I mentioned as well just as an example of how that could work!

Reply


Terms & Conditions