CODEWITHSUNDEEP
excel
user2284021
user2284021

Reputation: 31

Left / Right Functions in Excel

I'm working in Excel and I have this formula : 

=IF(B9="1","1/1",IF(B9="2","1 & 2",IF(ISNUMBER(SEARCH("/",B9)),B9,
IF(ISNUMBER(SEARCH("-",B9)),LEFT(B9,FIND("-",B9)-1)&" thru 
"&RIGHT(B9,FIND("-",B9)-1),"1 thru "&VALUE(B9)))))

where B9="9-24"

Why does this formula output "9 thru 4"?

I want it to output "9 thru 24". It's weird because if B9="18-24", for example, then this formula correctly spits out "18 thru 24".

There seems to be an issue, however, if the number preceding the dash is a single digit number. And I'm stuck.

Upvotes: 0

Views: 869

Answers (3)

Vicky
Vicky

Reputation: 13244

So the bit of that long formula that is actually relevant is:

LEFT(B9,FIND("-",B9)-1) & " thru " & RIGHT(B9,FIND("-",B9)-1)

If B9 contains the text "9-24" (without the quotes) then FIND("-",B9) gives 2.

So your formula becomes LEFT(B9,1) & " thru " & RIGHT(B9,1)

which gives you the left most single character and the right most single character, so "9 thru 4".

If B9 contains the text "18-24" (without the quotes) then FIND("-",B9) gives 3 and you get the left most two characters and the right most two characters.

If you want the parts of the cell before and after the "-", you need to change the "RIGHT" part of your formula to:

RIGHT(B9,LEN(B9) - FIND("-",B9))

Upvotes: 6

Trenin
Trenin

Reputation: 2063

The function RIGHT will return the rightmost characters of the string. For example:

RIGHT("hello", 1)

Will return o.

In your function, you are doing the following:

RIGHT(B9,FIND("-",B9)-1)

This is taking the position of "-" subtract 1 as the number of characters to use from the right. So you are using the length of the first number as the length for the second number.

Instead, you should be going the following:

RIGHT(B9,LEN(B9)-FIND("-",B9))

This will get you the rightmost characters after the "-".

Upvotes: 0

Jeff
Jeff

Reputation: 1807

You're using RIGHT() where you should be using MID(). To use MID(), you need a third argument long enough to capture everything after the dash. This works:

=IF(B9="1","1/1",IF(B9="2","1 & 2",IF(ISNUMBER(SEARCH("/",B9)),B9,
IF(ISNUMBER(SEARCH("-",B9)),LEFT(B9,FIND("-",B9)-1)&" thru 
"&MID(B9,FIND("-",B9)-1,99),"1 thru "&VALUE(B9)))))

Upvotes: 0

Related Questions