"if" formula not working- seems to be formatting

[GaiGauci]

Hi All
I have a formula in Col B =if(G2>"99999","StaffType1","StaffType2"),"")
Col G has the staff employee numbers in it which if they are a 5 digit
number they show a StaffType2 officer and if a 7 digit number show
StaffType1. I use this with the download from SAP which is cut and pasted
into D2 to O2 onwards.

My formula was working. But if I keep trying it (and I can't seem to
deliberately replicate), sometimes it shows everyone as StaffType1.

I am thinking a format problem in column G as if I just click in each G cell
and enter, it seems to correct itself. But I can't do this for all the cells
or I'll go mad!! I have tried General, text and number formats because I am
running out of ideas.

Cheers
Gai

 

[T. Valko]

=if(G2>"99999","StaffType1","StaffType2"),"")

Maybe this...

=IF(B2="","",IF(--G2>99999,"StaffType1","StaffType2"))

 

[Jacob Skaria]

--Enter 0 in a cell. Copy the cell
--Keeping the copy Select column G.
--Right click>PasteSpecial>Add. Click OK.

Now try

=if(G2>99999,"StaffType1","StaffType2")


If this post helps click Yes

 

[Ms-Exl-Learner]

Just paste this formula in B2 cell

=IF(--TRIM(LEN(G2))=5,"STAFF TYPE2",IF(--TRIM(LEN(G2))=7,"STAFF TYPE1",""))

Now copy the B2 cell formula and apply it for the remaining cells.

Change the cell reference G2 to your desired cell if required.

If this post helps, Click Yes!

 

[GaiGauci]

Thanks, that worked great and I learnt a bit more!!
Cheers
Gai