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?

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 Feb 17 at 14:10
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)
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 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 at 15:45
  • @Marco, For 3 times, things will be a bit more complicated. I will update the formula later
    – Jerry_MSFT
    Feb 19 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. Feb 19 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 at 1:37

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.