none
Formula for calculated column in SharePoint 2010 to get sub-string from another field

    Question

  • I have a name colume (file name) that has such format: A678098-completed evaluation

    I want extract the first 7 characters, can be a mix of alpha and numeric, into another field called PIN which is calculated field.

    First I tested this works:

    =Title  [this simply takes the entire string of the 'name' column to the new PIN column]

    Then I tried following formula to get just the first portion, but none of them work:

    =LEFT([Title],LEN([Title])-FIND("-",[Title]))  - This one gave an syntax error

    =RIGHT([Title],7)  - This one returned nothing

    Any one has a correct formula that can do what I want?

    Thanks in Advance!

    Thursday, July 26, 2012 7:05 PM

Answers

  • I presume you have Title column populated with the file name: "A678098-completed evaluation". As seen in the options of "Insert Column:", Name column cannot be used for reference in a Calculated Column.

    I tested against a List and it worked fine.

    Calculated Column


    • Edited by Guru Karnik Thursday, July 26, 2012 8:48 PM
    • Marked as answer by QuickLaunch Friday, July 27, 2012 4:22 PM
    Thursday, July 26, 2012 8:46 PM

All replies

  • Microsoft SharePoint Foundation formulas for calculated fields are based on Microsoft Excel functions and syntax. However, Microsoft supports only those functions mentioned on this page for use in SharePoint Foundation calculated fields. For example, the Excel functions MID and FIND are not supported.

    http://msdn.microsoft.com/en-us/library/bb862071.aspx

    Try using SEARCH instead of FIND.

    =LEFT(Title,SEARCH("-",Title)-1)


    Thursday, July 26, 2012 7:24 PM
  • I put that formula in (but put [ and ] around "Title", otherwise it gives and error" column does not exist. the result is the calculated column showed #VALUE!
    Thursday, July 26, 2012 8:37 PM
  • I presume you have Title column populated with the file name: "A678098-completed evaluation". As seen in the options of "Insert Column:", Name column cannot be used for reference in a Calculated Column.

    I tested against a List and it worked fine.

    Calculated Column


    • Edited by Guru Karnik Thursday, July 26, 2012 8:48 PM
    • Marked as answer by QuickLaunch Friday, July 27, 2012 4:22 PM
    Thursday, July 26, 2012 8:46 PM
  • Yes, you're right. I put the name into Title field and the formula worked. first portion before '-' was extracted correctly.

    I wonder if there is a way to copy file name into 'Title' field without coding?  I guess only way is to use workflow?

    Friday, July 27, 2012 2:29 PM
  • An Event Receiver would be the ideal option. There's no OOTB activity to do a system update in a SharePoint Designer workflow.
    Friday, July 27, 2012 4:02 PM
  • Hi Guru,

    I tried to post another question, but the site does not display forum to select for SharePoint 2010, so have to use this old thread to post my new question. It is related to the old one.

    What is the formula to round up a date field to the year end in sharepoint 2010 library?

    I want to round up the "created' date value to the year end in a calculated field (Record Year End). The new field can then be used for another function.

    e.g. created=9/25/2012, I want "Record Year End" to show: 12/31/2012

    I tried:

    ="12/31/"+YEAR(Created).  Result: Record Year End= 1

    =DATE(MONTH(12),DAY(31),YEAR(Created)). Result: Record Year End=12/2/1908

    Do you or anyone have any idear ?

    Thanks in advance!

    Tuesday, September 25, 2012 9:00 PM
  • The below formula works correctly for me. Have you set the calculate value data type as Date and Time? What's your farm patch level?

    =DATE(YEAR(Created),"12","31")


    • Edited by Guru Karnik Tuesday, September 25, 2012 11:04 PM Added question for farm patch level
    Tuesday, September 25, 2012 11:01 PM
  • Hi Guru,

    This works beautifully for me too! 

    Now my question is:

    how to get this to work in the "calculated value" of a "date" field, vs. "calculated" field, because the output field must be a "date" field in order to be used for IM Policy. It appears that the "calculated value" box of a "Date" field does not take reference to a field. I got error message when tried this formula in that box.

    Thanks a bundle!


    • Edited by QuickLaunch Wednesday, September 26, 2012 2:03 PM
    Wednesday, September 26, 2012 2:00 PM