0

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.

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))

enter image description here

0

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.

enter image description hereenter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.