 # 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

try =IF(C15<18,LEFT(E8,Search("/",E8)-1)+IF(C15>18,RIGHT(E8,Len(E8)-Search("/",E8)))

• 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. 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

Try:

=IF(C15<18,LEFT(E8,FIND("/",E8)-1))+IF(C15>18,RIGHT(E8,LEN(E8)-SEARCH("/",E8)))

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

What's the formula to return if C15=18?

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• 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

Barryj, It tests alright here.

## Files

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

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

The file displays the formulas that I had, when C15 is less than 18 it should display the number to the left and over 18 the number to the right.

As you can see from the attachment some cells only contain single digit numbers.

Thanks for the help.

## Files

• 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 understand, Brian, just expressing my annoyance, not at you though!

• 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.

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

barryj,

look at this attachment

filippo

## Files

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

Filo65, there is nothing showing from your post?

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

Quote

If it is not working for you, please attach a workbook showing the error.

I don't see you attaching, so I assume it does work now.

## Participate now!

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