SharePoint calculated column Extract sub-string before third occurrence of character.

Question

_{Monday, January 5, 2015 8:00 AM}

Dear Friends,

I want to extract the particular sub-string using calculated column.

The values in column1  are like ET-2015-67-1, ET-2015-67, ET-2015-67-5 , TS-2017-67-1,  TS-2017-1

I want to extract the sub-string before third occurrence of "-" .

Some times "-" occurred only two time in this case kept string as it is.

Resultant calculated column2 should contain values like  ET-2015-67, ET-2015-67, ET-2015-67 , TS-2017-67,  TS-2017-1.

So please help me develop formula fro same.

Thanks,
Digambar K.

Thanks and Regards, Digambar Kashid

All replies (3)

_{Monday, January 5, 2015 9:01 AM ✅Answered | 1 vote}

Try this

=IF(ISNUMBER(FIND("-",[ColumnName],9)),(LEFT([ColumnName],FIND("-",[ColumnName],9)-1)),[ColumnName])

Cheers,

_{Monday, January 5, 2015 8:46 AM}

Hi,

Please refer to the below link

https://social.technet.microsoft.com/Forums/sharepoint/en-US/854f7d87-dd3a-4b8d-a8bb-b826182c11af/formula-for-calculated-column-in-sharepoint-2010-to-get-substring-from-another-field?forum=sharepointadminprevious

Thanks,
Vivek
Please vote or mark your question answered, if my reply helps you

_{Tuesday, January 6, 2015 10:06 AM}

Hi Digambar,

You can use the formula below:

=IF(ISNUMBER(FIND("-",RIGHT([Column1], LEN([Column1])-8))),LEFT([Column1], 8+FIND("-",RIGHT([Column1], LEN([Column1])-8))-1),[Column1])

Best regards.

Thanks

Victoria Xia
TechNet Community Support