Sharepoint Calculated Column - Get last instance of substring

Question

I need to get the last value after , from the string.

e.g. 10%,90%,12%

It should return 12%

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

I used the formula above and it returned 90%,12%, even if I loop it. It can have 5-6 comma-separated values.

Answer 1

Providing the formula with some assumptions. Need to update based on your input text.

1. Assuming there is a [space] between comma and the next value like 23%, 50%
2. Assuming percent value can be 1-digit (8%), 2-digit (88%) or 3-digit (546%)

=IF(ISERROR(SEARCH(",",[COL], LEN([COL])-3)), IF(ISERROR(SEARCH(",",[COL], LEN(COL)-4)), IF(ISERROR(SEARCH(",",[COL], LEN([COL])-5)), "", RIGHT([COL],4)), RIGHT([COL],3)),RIGHT([COL],2))

Answer 2

You can have a try with the formula below, the digits simply does not affect the outcome.

=MID(MID(FieldName,(INT(FIND(",",FieldName))+1),30),(INT(FIND(",",MID(FieldName,(INT(FIND(",",FieldName))+1),30)))+1),30)

The number 30 is just bigger enough to make the MID function get the whole string.