Need help creating nested IF and LEFT formula in a calculated column

[Joe Capel]

Hey!

I have the following data in a text column called [Workcenter]

1 SOCS
CSS
SCXP
SCQA
SCOO
SCPR

I need a formula which will create the following results

CC
CC
SCX
SCQ
SCO
SCP

I'm trying to combine these 2 working formulas

=IF([Workcenter]="1 SOCS","CC",IF([Workcenter]="CSS","CC"))
=LEFT([Workcenter],LEN([Workcenter])-1)

I tried this formula without success

=IF(Workcenter="1 SOCS","CC",IF(Workcenter="CSS","CC"(=LEFT([Workcenter],LEN([Workcenter])-1))))

Any ideas?

[#2 · Accepted solution]

Hi Joe

That should do the trick:

=IF(OR([Workcenter]="1 SOCS";[Workcenter]="CSS");"CC";LEFT([Workcenter];LEN([Workcenter])-1))

Depending on regional settings, replace semicolon with comma in case of issues.

Cheers

Martin

[#3]

Awesome! Thanks a bunch! Changed the semi-colons and it worked perfectly. Final formula looked lke this:

=IF(OR([Workcenter]="1 SOCS",[Workcenter]="CSS"),"CC",LEFT([Workcenter],LEN([Workcenter])-1))