Excel formula to get highest and lowest value and seperate it by --CodePudding

I'm looking for a way to get from this: 130 | 140 | 150 (that's one cell in excel)

these are all the heights suited for a product and i want to get one field showing the range

to this: 130 - 150

So it should basically separate the values with the "|" and then only take the highest and the lowest value and separating it with a "-"

Is there an easy way to get this done? It wouldn't be a problem if it takes a few steps

CodePudding user response：

I could imagine your input is not sorted? Try:

=TEXTJOIN(" - ",,CHOOSEROWS(SORT(--TEXTSPLIT(A1,,"|")),1,-1))

CodePudding user response：

You could try:

• Formula used in cell B1

=TEXTJOIN(" - ",,TAKE(SORT(TEXTSPLIT(A1,,"|")*1),{1,-1}))

Note: The 1 & -1 within curly braces of the TAKE() function.

• A positive value : Takes the number of row(s) from the start of the array;

• A negative value : Takes the number of row(s) from the end of the array.

Data used same as JvdV Sir's. Answer posted as an alternative, inspired by JvdV Sir's answer.