none
How to retrieve substring of another field using Calculated field RRS feed

  • Question

  • Hi Friends,

    i have a sharepoint List. In this List there is two field (Name Field & DisplayName Field). When user add item to the list and put Name Field value Beck, Jonathan S(LastName, FirstName Middle Initial), so in DisplayName field should automatically populate accordingly Jonathan S Beck(FirstName Middle Initial LastName) without comma. Using following formula i am getting only First name and MI but not getting Last name.

    =RIGHT([Name],LEN([Name])-INT(FIND(",",[Name])))

    using following formula getting Last Name also but problem is how can i join both formula together, so that it can disply Full Name in Calculate field.

    =LEFT([Name],INT(FIND(",",[Name])-1))

    Thanks in advance.

         
    • Edited by rakib1 Friday, November 2, 2012 1:31 AM
    Thursday, November 1, 2012 10:37 PM

Answers

All replies

  • Have you tried concatenating the two formulas?

    =concatenate(RIGHT([Name],LEN([Name])-INT(FIND(",",[Name]))),LEFT([Name],INT(FIND(",",[Name])-1)))

    something like that.  see if it works.


    Laura Rogers
    Rackspace: SharePoint Consulting
    Blog: http://www.wonderlaura.com
    Twitter: WonderLaura
    Books:Beginning SharePoint 2010: Building Business Solutions with SharePoint
    Using InfoPath 2010 with Microsoft SharePoint 2010 Step by Step

    Friday, November 2, 2012 1:39 AM
  • Hi Laura,

    Thanks for your reply. The following formula is working fine if my name field value is Johnson, Michael M and its giving me Michael M Johnson.

    =concatenate(RIGHT([Name],LEN([Name])-INT(FIND(",",[Name]))),LEFT([Name],INT(FIND(",",[Name])-1)))

    1. Johnson, Michael M------> Michael M Johnson (This is working fine)

    But what i need to modify the above formula if i want to achieve following patterns.

    2. Johnson, Michael M (OH)         i want to get---> Michael M Johnson (OH)

    3. Ali, Zubin (MX200)------>         Zubin Ali (MX200)

    4. 287 Park 3rd AV Control Rm-----> 287 Park 3rd AV Control Rm  (want to display in calculated field as it is in Name field)

    Please let me know if you have any suggestions that will be very appreciated.


    • Edited by rakib1 Monday, November 5, 2012 8:43 PM
    Monday, November 5, 2012 8:41 PM
  • Hi,

    Please use the following fomular to conver the four scenarios you mentioned:
    =IF([Name]="", "", IF(ISERROR(INT(FIND(",", [Name]))), [Name], IF(ISERROR(INT(FIND("(", [Name]))), concatenate(concatenate(RIGHT([Name], LEN([Name])-INT(FIND(",",[Name]))), " "),LEFT([Name],INT(FIND(",",[Name])-1))), concatenate(LEFT(RIGHT([Name],LEN([Name])-INT(FIND(",",[Name]))), INT(FIND("(", RIGHT([Name],LEN([Name])-INT(FIND(",",[Name])))) - 1)), LEFT([Name],INT(FIND(",",[Name])-1)), concatenate(" ", RIGHT([Name], LEN([Name]) - INT(FIND("(", [Name])) + 1))))))

    Thanks,
    Jinchun Chen


    Jinchun Chen(JC)
    TechNet Community Support

    • Marked as answer by rakib1 Thursday, December 6, 2012 12:32 AM
    Thursday, November 29, 2012 9:18 AM
    Moderator