1

I'm working on a SharePoint 2010 site where I have a field which contains three letter characters which are separated by a hyphen e.g. AMO-SMF.

I have created a calculation which can FIND and REPLACE one of these at a time, however, there are seven different three letter abbreviation I need to replace with a name. These are as follows:

=IF(ISERROR(FIND("AMO",Result)),Result,REPLACE(Result,FIND("AMO",Result),3,"Amosite"))

    =IF(ISERROR(FIND("CRO",Result)),Result,REPLACE(Result,FIND("CRO",Result),3,"Crocidolite"))

    =IF(ISERROR(FIND("CHR",Result)),Result,REPLACE(Result,FIND("CHR",Result),3,"Chrysotile"))

    =IF(ISERROR(FIND("UMF",Result)),Result,REPLACE(Result,FIND("UMF",Result),3,"Unknown Mineral Fibre"))

    =IF(ISERROR(FIND("ORG",Result)),Result,REPLACE(Result,FIND("ORG",Result),3,"Organic Fibre Type"))

    =IF(ISERROR(FIND("SMF",Result)),Result,REPLACE(Result,FIND("SMF",Result),3,"Organic Fibre Type"))

    =IF(ISERROR(FIND("NFD",Result)),Result,REPLACE(Result,FIND("NFD",Result),3,"No Fibres Detected"))

Is it possible to perform a loop in SharePoint 2010 calculated fields which will catch all of these instances?

0

Yes, you can but it going to be the most complex formula.

For testing purpose, I tried for only 2 replacement -

This formula is for single code replacement -

*A1 - Name of the SharePoint column

=(IF((ISERR(FIND("AMO",A1))),A1,REPLACE(A1,FIND("AMO",A1),3,"Amosite")))

Similarly for another code replacement -

=(IF((ISERR(FIND("CRO",A1))),A1,REPLACE(A1,FIND("CRO",A1),3,"Crocidolite")))

The looping would work if we copy the first formula and replace with A1 in 2nd formula i.e.

(IF((ISERR(FIND("CRO",(IF((ISERR(FIND("AMO",A1))),A1,REPLACE(A1,FIND("AMO",A1),3,"Amosite")))))),(IF((ISERR(FIND("AMO",A1))),A1,REPLACE(A1,FIND("AMO",A1),3,"Amosite"))),REPLACE((IF((ISERR(FIND("AMO",A1))),A1,REPLACE(A1,FIND("AMO",A1),3,"Amosite"))),FIND("CRO",(IF((ISERR(FIND("AMO",A1))),A1,REPLACE(A1,FIND("AMO",A1),3,"Amosite")))),3,"Crocidolite")))

here the bold part is 1st formula - this way keep on replacing the A1 in each of 7 code replacement formula.

FYI, excel got hanged after 5th code replacement. Also formula becomes too complex hence not recommended. It would be recommended for 3-4 replacement.

  • Easier might be to do each step in a separate Calculated column.. so A1 replaces a TLA (Three Letter Acro) then A2 works on A1, A3 works on A2 etc. Once everything works you can merge the columns if you want.. but to SharePoint it doesn't matter.. You can have 48 Calculated Columns in a List – Danny '365CSI' Engelman May 24 '17 at 8:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.