SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-260370%22%20slang%3D%22en-US%22%3ENeed%20help%20creating%20nested%20IF%20and%20LEFT%20formula%20in%20a%20calculated%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-260370%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHey!%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20the%20following%20data%20in%20a%20text%20column%20called%20%5BWorkcenter%5D%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E1%20SOCS%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ECSS%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESCXP%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESCQA%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESCOO%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESCPR%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20need%20a%20formula%20which%20will%20create%20the%20following%20results%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3ECC%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ECC%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESCX%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESCQ%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESCO%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESCP%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI'm%20trying%20to%20combine%20these%202%20working%20formulas%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%3DIF(%5BWorkcenter%5D%3D%221%20SOCS%22%2C%22CC%22%2CIF(%5BWorkcenter%5D%3D%22CSS%22%2C%22CC%22))%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%3DLEFT(%5BWorkcenter%5D%2CLEN(%5BWorkcenter%5D)-1)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20tried%20this%20formula%20without%20success%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%3DIF(Workcenter%3D%221%20SOCS%22%2C%22CC%22%2CIF(Workcenter%3D%22CSS%22%2C%22CC%22(%3DLEFT(%5BWorkcenter%5D%2CLEN(%5BWorkcenter%5D)-1))))%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EAny%20ideas%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-260370%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECalculated%20Column%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eformula%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Elist%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esharepoint%202013%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-261497%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20creating%20nested%20IF%20and%20LEFT%20formula%20in%20a%20calculated%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-261497%22%20slang%3D%22en-US%22%3E%3CP%3EAwesome!%20Thanks%20a%20bunch!%20Changed%20the%20semi-colons%20and%20it%20worked%20perfectly.%20Final%20formula%20looked%20lke%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(OR(%5BWorkcenter%5D%3D%221%20SOCS%22%2C%5BWorkcenter%5D%3D%22CSS%22)%2C%22CC%22%2CLEFT(%5BWorkcenter%5D%2CLEN(%5BWorkcenter%5D)-1))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-260626%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20creating%20nested%20IF%20and%20LEFT%20formula%20in%20a%20calculated%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-260626%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Joe%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20should%20do%20the%20trick%3A%3C%2FP%3E%3CP%3E%3DIF(OR(%5BWorkcenter%5D%3D%221%20SOCS%22%3B%5BWorkcenter%5D%3D%22CSS%22)%3B%22CC%22%3BLEFT(%5BWorkcenter%5D%3BLEN(%5BWorkcenter%5D)-1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDepending%20on%20regional%20settings%2C%20replace%20semicolon%20with%20comma%20in%20case%20of%20issues.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3EMartin%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Joe Capel
New Contributor

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 Replies
Highlighted
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

 

Highlighted

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))