[smartie91]

Hi All

I need help with a excel if statement please what i have is a list containing machine ids

T003478
N000299
T003067
TT90044
all the above are what we call FM only the first to chars should be used as there are over 2000 ids
the second digit is a zero

NR02297
TX00024
TR02175
TC12345
these are called SF

there for if C1 = T0 then print FM or c1=nr print SF



i have this so far

 
=IF((C1="TT"),FM,IF((C1="N0"),FM,IF((C1="NR"),SF,IF((C1="TR"),SF,"NONE")))) 

and the error i get is

 
#NAME?	TT90185
NONE	T003478
#NAME?	NR02297
NONE	TX00024
 

hope this make sence

thanks

Nigel



edit

I have got it to work if i only put 2 char in c1 useing this

 
=IF((E5="TT"),"FM",IF((E5="N0"),"FM",IF((E5="NR"),"SF",IF((E5="TR"),"SF",IF((E5="T0"),"FM",IF((E5="TX"),"SF","NONE")))))) 

how do i no only pick first 2 chars

also i need altogether 10 if statement i think excel only does 7 is that right

thanks

nigel

This post has been edited by smartie91: 19 June 2008 - 02:19 AM

[IcemanND]

I won't redo the whole line but you should get the idea:

=if (Left(C1,2)="TT","FM",if(Left(C1,2)="NR","SF","None"))

[smartie91]

Thanks IcemanND

I have this now

 
=IF(LEFT(G4,1)="F","CYLYPSO",IF(LEFT(G4,2)="TX","SF",IF(LEFT(G4,2)="N0","FM",IF(LEFT(G4,2)="TT","FM",IF(LEFT(G4,2)="T0","FM",IF(LEFT(G4,2)="TR","SF",IF(LEFT(G4,2)="NR","SF","CCM"))))))) 

which works fine But the problem is now i cant search from this column
A ..... B .......C.........D.......... E......... F
SF 753651 Address Name 2A TX00085
FM 787621 Address Name 2B NR03955
CCM 792373 Address Name 2C NR03024
SF 794187 Address Name 3B NR00127

Column A is the result of above

I need to display how many SF,FM,CCM, there are in 2A,2B.2C.

thanks for your help
Nigel

[jaclaz]

Wouldn't a "count if" function do?
http://www.ozgrid.co...el/count-if.htm

jaclaz