none
Calculated Field to return a text value

    Question

  • I'm trying to create a calculated field that will hit on keywords in folder names and return a value based on the keyword found. Obviously the folder name is contained in the "Name" field on my sharepoint site but how do I write a formula that will look for a specific word or phrase and return an assigned text value?

    Example: Name field contains a list of folders (see the name list below). I want the formula to search for "AAA" and return value "XYZ", I want to search for "BBB" and return value "ABC",and I want to search for "CCC" and return a null field.

    Name

    Folder AAA Z

    Folder BBB Z

    Folder CCC Z

    So we're all clear what I'm up against; right now I have a total of 45 folders (soon to be more) that I want to cut down to 5 or 6 after grouping by the returned value generated by the calculated field. I have zero experience and I'm not a formula or a code guy. I've spent quite a few hours so far but I'm getting nowhere.

    Friday, December 27, 2013 7:43 PM

Answers

  • I ended up using a custom content type based on a folder. Then created a custom column based on said content type, assigning keywords to the folders, and then grouping by the custom column. It involves more hands on going forward but I had no luck with the calculated field because of the restrictions on using the name field to extract information from. Seems really dumb as that is the one single field that people will always use.

    Thanks for your help Alex. Your suggestions didn't actually work but you got my brain spinning in the right direction.

    Wednesday, January 01, 2014 3:43 PM
  • Hi DKeller33

    I'm gladyou were able to get it working with content types but if the calculated column
    allows less hands-on maintenance I am guessing you'd probably prefer to use
    that solution. So I'd like to take one more stab at it if you will bear with me

    For your case, I created a new document library so I could test out the calculation.

    By default there were a number of columns created.

    One of them was the "Name" column which is also a hyperlink ( I beleive this is the column you are trying to use for the calculation)

    There is also by default a column named "Title"

    I used the "Title" column for my example because it is the same text as the folder's "Name" Column

    I created a calculated field called "Calculated Group"  and added the calculation

    =IF(ISNUMBER(FIND("AAA",Title)),"XYZ",IF(ISNUMBER(FIND("BBB",Title)),"ABC",IF(ISNUMBER(FIND("CCC",Title)),"","Next nested if")))

    The calculated column then returned the appropriate grouping as a string so you can group or filter by it.Please let me know if you get it to work or let me know that I'm just spinning my wheels and I will stop trying.

    Have a Great New Year! I hope SharePoint brings you good tidings!


    Alex


    Thursday, January 02, 2014 2:21 PM

All replies

  • I think I understand what you are trying to accomplish but its going to be a pretty big calculation as you add more strings to search for.

    Below is a calculation for your above example. you will need to add more nested if statements folowing my calculations pattern

    Assuming the column you are looking up is actually called "Name"

    =IF(ISNUMBER(FIND("AAA",Name)),"XYZ",IF(ISNUMBER(FIND("BBB",Name)),"ABC",IF(ISNUMBER(FIND("CCC",Name)),"","Next nested if")))

    Please let me know if you have any questions.

    Good Luck!


    Alex

    Friday, December 27, 2013 9:14 PM
  • Thanks Alex, I'm going to work on this today, and yes, the formula is going to be huge and get REAL confusing but the entire library will be next to useless for our users if we can't get these folders grouped in a sensible manner.
    Monday, December 30, 2013 12:39 PM
  • So it's not working. This is the formula I'm using:

    =IF(ISNUMBER(FIND("Account",["LinkFilename"])),"CommVault","")

    My problem is I get an error message that says the column name does not exist. You can verify the column name by sorting on it and checking the url. It will provide the internal name for the column which is what I have in my formula but I still get the error message. I've tried it with the [""] and without with the same result. ???????????????

    Monday, December 30, 2013 1:28 PM
  • Try this

    =IF(ISNUMBER(FIND("Account",[LinkFilename])),"CommVault","")

    I think the problem was that you had quotes around the field name.


    Alex


    • Edited by Alex Hornak Monday, December 30, 2013 4:25 PM
    Monday, December 30, 2013 4:24 PM
  • Nope, same result. Error message says column does not exist.

    In reading further everything I see says you can't reference the name field in a formula for a calculated column.


    • Edited by DKeller33 Monday, December 30, 2013 5:01 PM
    Monday, December 30, 2013 4:59 PM
  • I ended up using a custom content type based on a folder. Then created a custom column based on said content type, assigning keywords to the folders, and then grouping by the custom column. It involves more hands on going forward but I had no luck with the calculated field because of the restrictions on using the name field to extract information from. Seems really dumb as that is the one single field that people will always use.

    Thanks for your help Alex. Your suggestions didn't actually work but you got my brain spinning in the right direction.

    Wednesday, January 01, 2014 3:43 PM
  • Hi DKeller33

    I'm gladyou were able to get it working with content types but if the calculated column
    allows less hands-on maintenance I am guessing you'd probably prefer to use
    that solution. So I'd like to take one more stab at it if you will bear with me

    For your case, I created a new document library so I could test out the calculation.

    By default there were a number of columns created.

    One of them was the "Name" column which is also a hyperlink ( I beleive this is the column you are trying to use for the calculation)

    There is also by default a column named "Title"

    I used the "Title" column for my example because it is the same text as the folder's "Name" Column

    I created a calculated field called "Calculated Group"  and added the calculation

    =IF(ISNUMBER(FIND("AAA",Title)),"XYZ",IF(ISNUMBER(FIND("BBB",Title)),"ABC",IF(ISNUMBER(FIND("CCC",Title)),"","Next nested if")))

    The calculated column then returned the appropriate grouping as a string so you can group or filter by it.Please let me know if you get it to work or let me know that I'm just spinning my wheels and I will stop trying.

    Have a Great New Year! I hope SharePoint brings you good tidings!


    Alex


    Thursday, January 02, 2014 2:21 PM
  • Sorry for not getting back Alex. I had the title column originally but it was void of any data at all. The way I did it isn't ideal but after getting the foundation laid it isn't too much extra effort to maintain it. I may play some more though following your example.

    Thanks!!

    Tuesday, January 14, 2014 4:34 PM
  • if there is no info in the title field then it it was probably never filled out when new item was created.


    Alex

    Tuesday, January 14, 2014 7:13 PM