none
Calculated Columns

    Question

  • I am using office SharePoint 365 trying to extract data from a single column and split it up to form new columns. I have file names in this format

    Acc Num_Surname_First Name_Department

    I grab the Name with a workflow and copy it to the Title Field so I can run calculated column queries against it..

    I get the Acc Num using - =LEFT(Title,INT(FIND("_",Title)-1))

    I get the Surname using - =MID(Title,FIND("_",Title)+1,FIND("_",Title,8)-FIND("_",Title)-1)

    But I am struggling to collect both the 'First Name' and also the 'Department'

    Department

    if I use -  =RIGHT(Title,INT(FIND("_",Title)-1))

    I get 8 characters, and playing with the values reduces or adds to them but does not stop at an underscore

    First Name - I get nowhere. I tried some excel code but changing cells for SharePoint data still went nowhere..

    This works in Excel - =TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",100)),200),100))

    But SharePoint I know does not support SUB but also does not like this - =TRIM(LEFT(RIGHT(REPLACE([Title],"_",REPT(" ",100)),200),100))

    

    if anyone can help i would be grateful and if anyone knows how i would get further along the string (say if I had 4/5 underscores) that would be great too.

     

    • Edited by Tony Mayes Thursday, September 22, 2016 8:38 AM update
    Wednesday, September 21, 2016 7:03 PM

Answers

  •  Thanks for the reply - Not sure how screen shots would of helped to be honest... Alas

    I have now resolved the Issue by Stripping data into another column for each jump and using a calculated column against that one  :)

    • Marked as answer by Tony Mayes Friday, September 23, 2016 10:57 AM
    Friday, September 23, 2016 10:57 AM

All replies

  • if you can provide some screenshots

    it would be helpful

    Friday, September 23, 2016 9:08 AM
  •  Thanks for the reply - Not sure how screen shots would of helped to be honest... Alas

    I have now resolved the Issue by Stripping data into another column for each jump and using a calculated column against that one  :)

    • Marked as answer by Tony Mayes Friday, September 23, 2016 10:57 AM
    Friday, September 23, 2016 10:57 AM
  • Hi Tony,

    Thanks for your sharing.

    It will be helpful to others.

    Best Regards,

    CY


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, September 26, 2016 1:07 AM