Find Max and Min values in one cell
Hi All!
I was hoping someone could help me please?
I am looking to find the Max and Min values from a cell containing a range of values separated by ";"
E.g. 0;10;50;10;0
I need the results to show the Min "0", and the Max "10".
Is there any way to do this?
Thanks!!!
Best Answer
-
Below is a formula you can use, replace the MAX for MIN to get the lowest number. This will work up till 10 numbers you can add to it if needed.
=MAX(IFERROR(VALUE(LEFT([Input Values]@row + ";", FIND(";", [Input Values]@row + ";") - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 1)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 2)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 1)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 2)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 3)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 2)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 3)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 4)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 3)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 4)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 5)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 4)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 5)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 6)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 5)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 6)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 7)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 6)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 7)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 8)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 7)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 8)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 9)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 8)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 9)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 10)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 9)) - 1)), ""))
Answers
-
You would need to parse out each of the options into their own cells. How many total options could there be within a single cell?
-
Hi @Paul Newcome,
The total options can vary but I could guess a max of about 10.
Can you tell me how to parse out the values please? I've tried some of your past solutions but can't seem to get this to work for me here.
I've attached a screenshot below of a example data set that I need to assess for Max and Min, I would really appreciate any help!
Thanks!
-
Which solution(s) have you tried? You are going to need at least 10 helper columns. One for each option.
-
No problem with helper columns, I can just hide these.
I have tried all of the below and about 100 more! Lol
https://community.smartsheet.com/discussion/66859/pull-text-from-string-using-delimiter
The Sheet referenced in the above link seems to work vertically, so doesn't suit my data.
-
No problem adding helper columns, I can just hide these.
I tried all the below and about 100 more haha
https://community.smartsheet.com/discussion/55721/extract-part-of-a-cells-text
The sheet referenced in the above link seems to only work vertically which doesn't suit my data set
-
Below is a formula you can use, replace the MAX for MIN to get the lowest number. This will work up till 10 numbers you can add to it if needed.
=MAX(IFERROR(VALUE(LEFT([Input Values]@row + ";", FIND(";", [Input Values]@row + ";") - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 1)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 2)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 1)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 2)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 3)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 2)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 3)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 4)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 3)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 4)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 5)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 4)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 5)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 6)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 5)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 6)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 7)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 6)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 7)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 8)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 7)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 8)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 9)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 8)) - 1)), ""), IFERROR(VALUE(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 9)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 10)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 9)) - 1)), ""))
-
@Leibel Shuchat this has worked perfect! Thank you so much, this has saved me a lot of work 😀
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.1K Get Help
- 444 Global Discussions
- 142 Industry Talk
- 472 Announcements
- 5K Ideas & Feature Requests
- 83 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 489 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
