0

All,

i am trying to extract part of a string in a calculated field. The calculated field is looking in the title field, of an item in the library, for the word "DIS". The formula I am using is the following: =IF(ISNUMBER(FIND("DIS",Title,10)),MID(Title,INT(FIND("DIS"),Title,10)),13),"")

When it finds the word "DIS" I want the code to extract 13 characters. This code is working fine until the title consists of more than one instance of "DIS". For example when it runs into the word "DISTANCE". I tried to get the last instance using RIGHT() function in the formula because the characters I am looking for are always somewhere at the end of the title. I cant get this to work. Can anyone help with that?

Improve this question
1
  • Could you provide few sample values of [Title] column showing literal "DIS". Is there any pattern as to maximum number of "DIS" occurrences in [Title] column? If that is known, maybe you could use SEARCH() function. See here
    – Matiur Rahman
    Feb 17, 2021 at 14:10

1 Answer 1

Reset to default
1

If the word "DIS" will only appear once or twice, then we can add a If condition to help us get the last "DIS". RIGHT() needs the correct index so it will not be the function for this scenario. My formula is like this

=MID(Text,IF(ISNUMBER(FIND("DIS",Text,(FIND("DIS",Text,1)+1))),FIND("DIS",Text,(FIND("DIS",Text,1)+1)),FIND("DIS",Text,1)),13)

The word text refers to the column you are searching, just use Title in you list.

The demo works like below:

enter image description here

Update: The new formula below shall work for the worst case (DIS appears 3 times)

=MID(A1,IF(ISNUMBER(FIND("DIS",A1,(FIND("DIS",A1,(FIND("DIS",A1,1)+1))+1))),FIND("DIS",A1,(FIND("DIS",A1,(FIND("DIS",A1,1)+1))+1)),IF(ISNUMBER(FIND("DIS",A1,(FIND("DIS",A1,1)+1))),FIND("DIS",A1,(FIND("DIS",A1,1)+1)),FIND("DIS",A1,1))),13)
Improve this answer
6
  • The word DIS could appear like 2 or 3 times in a worse case. The occurance we need is always the last one. It would appear in the text like I mentioned. DISCOVERY.OF.THE.MOON.DIS2021434656
    – Marco
    Feb 18, 2021 at 15:06
  • Jerry, I tested your code and it works fine when DIS appears 1 or 2 times. If it appears 3 times it doesn't work.
    – Marco
    Feb 18, 2021 at 15:45
  • @Marco, For 3 times, things will be a bit more complicated. I will update the formula later
    – Jerry_MSFT
    Feb 19, 2021 at 1:14
  • @Jerry_MSFT thanks for helping me out. I know theproblem with this problem is basically I dont know how many timethe word DIS can occure. Can be from 0 to 3 times.
    – user14010662
    Feb 19, 2021 at 11:17
  • 1
    @user14010662, yes the new one works for 1 to 3 times. If 0 times, what do you want to show.
    – Jerry_MSFT
    Feb 22, 2021 at 1:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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