Share via

SP Calculated column to grab value between two delimiters

SharePoint Automation 1 Reputation point
2022-07-07T18:59:31.667+00:00

Hello, I have a unique id column using a Work Breakdown Structure. I would like to make a calculated column that pulls whatever is in between 2 periods.

EX:
I5.XYZ.241234 should grab "XYZ"
I7.R&DFINTECH.149876 should grab "R&DFINTECH"

218716-image.png should grab "PIC"

Thought this would be a couple of splits and id be done with it but apparently not!

SharePoint Development
SharePoint Development
SharePoint: A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.Development: The process of researching, productizing, and refining new or existing technologies.
2,810 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Dillon Silzer 56,681 Reputation points
    2022-07-07T20:12:26.317+00:00

    My solution might not be the cleanest way but it gets the job done. If it helps you please don't forget to accept as answer. Thanks!

    218734-image.png

    Create a column to extract everything after the first character (I called my TitleCalc)

    =RIGHT(Title,LEN(Title)-FIND(".",Title,FIND(".",Title)-1))

    Create a column to extract everything after the first character of TitleCalc (I called my TitleCalc2)

    =LEFT(TitleCalc,INT(FIND(".",TitleCalc)-1))

    You can then hide the TitleCalc column as seen below:

    218763-image.png

    How to create calculated column:

    https://support.microsoft.com/en-us/office/calculate-data-in-lists-or-libraries-c5261743-667f-4833-bede-e516cef2a0e1

    Formulas cited from:

    https://sharepoint.stackexchange.com/questions/29140/using-calculated-field-to-retrieve-substring-of-another-field

    0 comments
  2. Xuyan Ding - MSFT 7,561 Reputation points
    2022-07-08T06:02:43.687+00:00

    Hi @SharePoint Automation ,

    You could try the following code. 

    =LEFT(RIGHT(Title, LEN(Title)-FIND(".",Title)),FIND(".",RIGHT(Title, LEN(Title)-FIND(".",Title)))-1)

    218795-image.png 218808-image.png

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.