Split column based on multiple delimiters

[Mellissa Perez]

Hi.  I have a column that uses a ">" as a delimiter.  The problem is is that there can be multiple ">"s and the data is not always the same length. 

Ex:  

A > B > C

A > B > C > D

A > B

A

What i need to do is pull the data after the last delimiter and if there is no delimiter, have the data saved as is.

Is it possible to split the data after the last delimiter?

Thank you for your time!

[#2]

@Mellissa Perez If you just want to show this trimmed data in list view and not going to use anywhere else (list filtering/sorting, power app, power automate, power bi, export to excel, etc.) later, you can easily achieve it using JSON formatting.

Use JSON like: 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if(indexOf(@currentField, '>')==-1,@currentField,substring(@currentField, lastIndexOf(@currentField, '>')+2, indexOf(@currentField + '^', '^')))"
}

Note, I have used +2 in JSON as I have > and space after each letter like: A > B > C. You can adjust it as per your requirements.

List view before applying JSON: 

List view after applying JSON: 

Documentation: SharePoint JSON column formatting 

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#3]

@Mellissa Perez If you need trimmed data for later use as mentioned in above post, you will have to trim the data at source before adding to SharePoint list. How you are adding date to SharePoint list, manually/programmatically/excel import/using Power apps?

Or if you have already added data to SharePoint list, you can try running Power automate flow, do trimming operations in flow using expressions and save the trimmed results back to SharePoint list using update item action with SharePoint connector in power automate flow.

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#4]

@ganeshsanap 

Hi and thank you.  Unfortunately the data cannot be trimmed before adding to the sharepoint list.  The idea is that 1. that datafield could be used to look up another value on a separate sharpeoint list and 2.  both values would be added to a microsoft work template.  This template will be converted to a pdf.

[#5 · Accepted solution]

@Mellissa Perez Run Power automate flow on item creation and/or item update (as per your requirements).

Then perform trim operations as mentioned above in power automate flow using flows expressions (similar functions like substring, indexOf are available).

Save back the trimmed result to SharePoint list using update item action. You can add trigger condition to avoid infinite flow running loop - condition based on whether > is found in the column value.

You can also try calculated column approach to create a new column based on the existing column ( having values with > ). But, you might not be able to use the calculated columns in the SharePoint lookups.

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.