LEFT Worksheet function

[loren.pottinger]

=IF(LEFT(G7,2) = 17,"Dep", "Amort")

I constructed the above function to return Dep if the first two digits
starting from the leftmost digit in cell G7 is 17 and return Amort
otherwise, please help. This formula looks so right to me.

 

[Guest]

How about =IF(LEFT(G7,2)="17","Dep","Amort")

Dave

 

[Guest]

Try:

=IF(VALUE(LEFT(G7,2) )= 17,"Dep", "Amort")

if G7 is text

=IF(LEFT(G7,2) = "17","Dep", "Amort")

HTH

 

[Guest]

If G7 is text or numbers, putting 17 in quotation marks will return the right
answer.

So, all that is neede is =IF(LEFT(G7,2)="17","Dep","Amort")

From what I can tell, LEFT treats any string of characters as text, even if
they are formatted differently than text.

Dave

 

[Trevor Shuttleworth]

Try:

=IF(LEFT(G7,2) = "17","Dep", "Amort")

or

=IF(--LEFT(G7,2) = 17,"Dep", "Amort")

Regards

Trevor

 

[Guest]

The function LEFT expects text data. Its argument (which I suspect is a
number) should therefore be first converted to text.
Also the quantity it is compared to (namely 17) should be presented as text,
that is "17".

Try this:
=IF(LEFT(TEXT(G7,"#"),2) = "17","Dep", "Amort")

 

[Guest]

Well, the --LEFT(G7,2) tip is really clever !

Try:

=IF(LEFT(G7,2) = "17","Dep", "Amort")

or

=IF(--LEFT(G7,2) = 17,"Dep", "Amort")

Regards

Trevor

 

[loren.pottinger]

All,

I am baffled none of those work. They only return dep or Amort for all
the cells regardless of what is in column G.

 

[Guest]

The formula I give you above worked for me.

What happens if you use the formula I give you and then you press F9 (which
forces Excel to recalculate)?

Any luck?

 

[loren.pottinger]

Dave. When it is false, it is not returning Amort

 

[loren.pottinger]

Oooops. My bad it is working. Thank you.

The formula I give you above worked for me.

What happens if you use the formula I give you and then you press F9 (which
forces Excel to recalculate)?

Any luck?

 

[Trevor Shuttleworth]

I learned it here ;-)

Well, the --LEFT(G7,2) tip is really clever !