Left & Right Formula To Show Number Not/as Well

    I have numbers in a cell such as 1/25, 4/31, 12/35 etc I have the current formula to extract the left or right number but when there is only a single digit it also includes the / is there a way of eliminating this.


    The numbers vary from 1 to 2 digits.


    My current formula is =IF(C15<18,LEFT(E8,2))+IF(C15>18,RIGHT(E8,2))


    I only want it to show the number not the forward slash



    Thanks Barry.

    Re: Left &amp; Right Formula To Show Number Not/as Well


    =if(c15<18,left(E8,find("/",E8,1)-1)+if(c15>18,right(e8,2))

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: Left &amp; Right Formula To Show Number Not/as Well


    Thanks for the replys guys, but I am getting a false result when C15 is greater than 18 and I am only getting 1 number when C15 is under 18 ie: 1/25 is returning only 2 not 25.


    :(

    Re: Left &amp; Right Formula To Show Number Not/as Well


    Still only showing false for single digit from 1/25 number if C15 is under 18 and 2 if C15 is greater than 18?

    Re: Left &amp; Right Formula To Show Number Not/as Well


    Enter this in D9 and copy across =IF($C$15<=18,LEFT(D8,IF(ISERROR(SEARCH("/",D8)),LEN(D8),SEARCH("/",D8)-1)))+IF($C$15>18,RIGHT(D8,LEN(D8)-IF(ISERROR(SEARCH("/",D8)),0,SEARCH("/",D8))))

    Re: Left &amp; Right Formula To Show Number Not/as Well


    Brian, You appear to be answering a question other than what is asked in the first post, and which is not covered by the title of the thread...

    Re: Left &amp; Right Formula To Show Number Not/as Well


    Sorry Cringe. I'm just trying to avoid the evetnual next question that this causes problems when there isn't a / in the cell.

    Re: Left &amp; Right Formula To Show Number Not/as Well


    I tried both of Cringes formulas, they work for double digit numbers but not for a single digit.

    Re: Left &amp; Right Formula To Show Number Not/as Well


    Quote from Barryj

    I tried both of Cringes formulas, they work for double digit numbers but not for a single digit.


    Barryj, To clear this up for future readers, I gave you only one formula, which I attached in post #8. For two numbers separated by a forward slash, for example, 2/3, it DOES work. Although I followed your formula construction, which I would not have used myself, using two separate, but complete, IF statements, linked by a "+". (I would have used nested IFs.) I have tested this for single and multiple digit numbers before and after the slash character. If it is not working for you, please attach a workbook showing the error.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login