none
calculated field values RRS feed

  • Question

  • i have below fields in the list

    Empno //number field

    employee number ending-(to capture last 4 digits number from Empno field)

    example

    employee number :1234567890

    employee number ending:7890

    How to capture the values from Empno field  only last 4 digit numbers and display in employee number ending field .

    kindly help if we could set formula for the same


    Blitz

    Thursday, November 14, 2019 5:24 AM

Answers

  • Hi Blitz,

    There is a workaround, try to create some calculated columns to remove “-” character from the emp column as below:

    Create a new calculated column test1 with formula as:

    =LEFT(emp,INT(FIND("-",emp)-1))

    Create a new calculated column test2 with formula as:

    =RIGHT(emp,LEN(emp)-INT(FIND("-",emp)))

    Create a new calculated column test3 with formula as:

    =LEFT(test2,INT(FIND("-",test2)-1))

    Create a new calculated column test4 with formula as:

    =RIGHT(test2,LEN(test2)-INT(FIND("-",test2)))

    Create a new calculated column test5 with formula as:

    =LEFT(test4,INT(FIND("-",test4)-1))

    Create a new calculated column test6 with formula as:

    =RIGHT(test4,LEN(test4)-INT(FIND("-",test4)))

    Create a new calculated column  NumNoCharacter with formula as:

    =test1&test3&test5&test6

    Edit the employee number ending column formula as:

    =RIGHT(NumNoCharacter,4)

    You can hide these newly created calculated columns. The result as below:

    Best regards,

    Grace Wang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Thursday, November 14, 2019 8:14 AM
    Moderator

All replies

  • Hi Blitz,

    Whether the employee number ending field is a calculated column? If so, set the formula as:

    =RIGHT([Empno],4)

    Result displays as below:

    Best regards,

    Grace Wang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Thursday, November 14, 2019 7:03 AM
    Moderator
  • Thanks

    i have emp number (Single line of text field ) and sorry its not number field as per above i mentioned and it should be in below format with "-" so it should not consider special characters and how to eliminate - from the empno and display only last 4 digits 

    empno: 1-123-1234-12 // we have hypen format in empno

    employee number ending should not display 4-12

    expected output

    3412


    Blitz

    Thursday, November 14, 2019 7:27 AM
  • Hi Blitz,

    Whether the number of "-" in empno field is the same for each item?

    Best regards,

    Grace Wang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Thursday, November 14, 2019 8:04 AM
    Moderator
  • Yes it will be in the below format for each and every item

    empno: 1-123-1234-12


    Blitz

    Thursday, November 14, 2019 8:12 AM
  • Hi Blitz,

    There is a workaround, try to create some calculated columns to remove “-” character from the emp column as below:

    Create a new calculated column test1 with formula as:

    =LEFT(emp,INT(FIND("-",emp)-1))

    Create a new calculated column test2 with formula as:

    =RIGHT(emp,LEN(emp)-INT(FIND("-",emp)))

    Create a new calculated column test3 with formula as:

    =LEFT(test2,INT(FIND("-",test2)-1))

    Create a new calculated column test4 with formula as:

    =RIGHT(test2,LEN(test2)-INT(FIND("-",test2)))

    Create a new calculated column test5 with formula as:

    =LEFT(test4,INT(FIND("-",test4)-1))

    Create a new calculated column test6 with formula as:

    =RIGHT(test4,LEN(test4)-INT(FIND("-",test4)))

    Create a new calculated column  NumNoCharacter with formula as:

    =test1&test3&test5&test6

    Edit the employee number ending column formula as:

    =RIGHT(NumNoCharacter,4)

    You can hide these newly created calculated columns. The result as below:

    Best regards,

    Grace Wang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Thursday, November 14, 2019 8:14 AM
    Moderator
  • Hi Blitz,

    Is there any update?

    If the post helps you, you can mark it as answer.

    Best regards,

    Grace Wang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Friday, November 15, 2019 9:06 AM
    Moderator
  • Hi Blitz,

    Thanks for marking my reply as answer. I’m pleased to know that the information is helpful to you. I hope you are delighted with the service we provided you.

    Here I will provide a brief summary of this post for your information.

    [calculated field values]

    Environment

    ====================

    SharePoint 2010

    Question

    ====================

    Blitz has a number field with the format as 1-123-1234-12, want to display only last 4 digits without “-” in another calculated column/

    Solution

    ====================

    I give a workaround, try to create some calculated columns to remove “-” character from the emp column as below:

    Create a new calculated column test1 with formula as:

    =LEFT(emp,INT(FIND("-",emp)-1))

    Create a new calculated column test2 with formula as:

    =RIGHT(emp,LEN(emp)-INT(FIND("-",emp)))

    Create a new calculated column test3 with formula as:

    =LEFT(test2,INT(FIND("-",test2)-1))

    Create a new calculated column test4 with formula as:

    =RIGHT(test2,LEN(test2)-INT(FIND("-",test2)))

    Create a new calculated column test5 with formula as:

    =LEFT(test4,INT(FIND("-",test4)-1))

    Create a new calculated column test6 with formula as:

    =RIGHT(test4,LEN(test4)-INT(FIND("-",test4)))

    Create a new calculated column  NumNoCharacter with formula as:

    =test1&test3&test5&test6

    Edit the employee number ending column formula as:

    =RIGHT(NumNoCharacter,4)

    Finally hide these newly created calculated columns.

    Best regards,

    Grace Wang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Wednesday, November 27, 2019 2:53 AM
    Moderator