LEFT formula, cell format as number?

**netvic** · 10-08-2010, 05:44 AM

In K column I always enter a size with letter x in the middle. For instance 28x56 in K8.

In the L column I have a LEFT formula, in L8 it is =LEFT(K8;2) which gives me the answer 28. In M I make the same with RIGHT formula which gives me 56. Everything is ok so far.

In N column I enter how many pieces I need of this part. For instance 20 in N8.

In the end of this row I need to see what L8 * N8 is with this formula
=IF(K8=28;L8*N8;"")

However, although K8 is 28 it gives me nothing. I guess it is because K8 is a formula which gives me 28, but the value isn't actually 28.

How can I make this formula work?

I know I can enter the LEFT formula inside the IF formula but I prefer to have two different cells.

**Andy Pope** · 10-08-2010, 06:10 AM

But K8 is not 28 it is 28x56

Try this formula to return the value 1568.

=IFERROR(L8*M8;"")

Why not enter the two values, 28 and 56, in separate columns?

**daddylonglegs** · 10-08-2010, 06:31 AM

Originally Posted by netvic

In the L column I have a LEFT formula, in L8 it is =LEFT(K8;2)

so shouldn't you be checking L8 for 28 not K8?

LEFT function always returns a text value; try converting to a number like this

=LEFT(K8;2)+0

then this formula should work

=IF(L8=28;L8*N8;"")

**DonFlak** · 10-08-2010, 09:36 AM

In the example you provided you have entered, in your words, "For instance 28x56 in K8."

With this in mind, your formula will produce no result as K8 does not equal 28. Additionally, it may just be a type but your formula has semi0colons instead of commas. Should read =IF(K8=28,L8*N8,"")
but I suspect you should read =IF(L8=28,L8*N8,"")

**DonFlak** · 10-08-2010, 09:41 AM

If you are having issues wiith it not recognizing L8 as a number include the 28 in quotes like

=IF(L8="28",L8*N8,"")

This would provide the correct result of 560

**Andy Pope** · 10-08-2010, 09:47 AM

I see I screwed up my cell references and expected result.

Try this formula to return the value 560.

=IFERROR(L8*N8;"")

@DonFlak, semicolon separators are fine for some non USA version of excel.

**netvic** · 10-08-2010, 07:10 PM

Originally Posted by DonFlak

If you are having issues wiith it not recognizing L8 as a number include the 28 in quotes like

=IF(L8="28",L8*N8,"")

This would provide the correct result of 560

I spelled wrong here. In excel the IF formula of course was L8 and not K8, so that was not the solution.

But when I added quotes in the formula everything is working as it should.

Thank you DonFlak!