# Get the desired number from a range

##### Member
Dear all,

I hope everyone is well. I am given a price range daily and I wanted to know what formula I can use to get the daily high and low from each range daily.

I have attached a screenshot. The desired output is in yellow

TIA

#### Attachments

• 25.7 KB Views: 32

#### Marc L

##### Excel Ninja
Hi !​
If cell B2 contains the range like '50-100' so the beginner level formula for cell​
• B3 is `=LEFT(B\$2,SEARCH("-",B\$2)-1)`

• B4 is `=RIGHT(B\$2,LEN(B\$2)-SEARCH("-",B\$2))`

##### Member
Many many thanks! I was using the left and right functions, but then number of digits was changing frequently. Really appreciate your help!

Last edited by a moderator:

#### Peter Bartholomew

##### Well-Known Member
A cunning trick for your amusement. Naming the numeric ranges to be 'range', allows the formula
= IMREAL(range&"i")
to give the lows and
= -IMAGINARY(range&"i")
gives the highs.

##### Member
Hi !​
If cell B2 contains the range like '50-100' so the beginner level formula for cell​

• B3 is `=LEFT(B\$2,SEARCH("-",B\$2)-1)`

• B4 is `=RIGHT(B\$2,LEN(B\$2)-SEARCH("-",B\$2))`
Hi After I used the formula to find the daily high and low, I tried to find the highest and lowest numbers for the whole week using the MIN and MAX formula, but the answer comes as 0.

Any idea how to solve this please?

#### Peter Bartholomew

##### Well-Known Member
Anything extracted using the text functions LEFT, RIGHT or MID will be returned as a text string. The function VALUE will coerce the text to a number, as will double negation.
`=VALUE(LEFT(B\$2,SEARCH("-",B\$2)-1))`
`=--LEFT(B\$2,SEARCH("-",B\$2)-1)`

Last edited by a moderator:

#### pecoflyer

##### Active Member
Perhaps post a sample sheet with some expected results calculated manually. At the hand of your very scarce information AGGREGATE might be the answer

`=VALUE(LEFT(B\$2,SEARCH("-",B\$2)-1))`
`=--LEFT(B\$2,SEARCH("-",B\$2)-1)`