# 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!!!

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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?

• 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

• ✭✭✭✭✭✭