I am currently looking at data that looks like this in column L:
1) BAVP, Bay Partners X
2) Bay Partners X, Telos Venture Partners II
3) Adams Street Direct Fund IV, Apex Investment Fund IV, Bay Partners X, Thomas Weisel Strategic Opportunities
Current Formula: IF(FIND("Bay Partners",L21) = 1,(MID(L21,FIND("Bay Partners",L21),FIND(",",L21,FIND("Bay Partners",L21))-FIND("Bay Partners",L21))),(MID(L21,FIND("Bay Partners",L21),FIND(",",L21,FIND("Bay Partners",L21)-1)-FIND("Bay Partners",L21))))
Currently, my formula can account for edge cases 2 and 3, however, I am having trouble creating a statement that can account for edge case 1, where there are no delimiters to search for at the end of the text.
Case 2 = Find when word is in the beginning. I search to see if the Find function returns 1, if so then I can account for when the name is in the beginning
Case 3 = Find when word in the middle. Searches for the comma. and if Find is not 1 I do -1 in the find to eliminate the , when it displays the text.
Case 1 = Find when word is at the end. I cannot figure this part out since there are no indications of the end.
I want to keep the data the way it is. This formula will be used across many more spreadsheets.
Thank you in advance for your help!!
1) BAVP, Bay Partners X
2) Bay Partners X, Telos Venture Partners II
3) Adams Street Direct Fund IV, Apex Investment Fund IV, Bay Partners X, Thomas Weisel Strategic Opportunities
Current Formula: IF(FIND("Bay Partners",L21) = 1,(MID(L21,FIND("Bay Partners",L21),FIND(",",L21,FIND("Bay Partners",L21))-FIND("Bay Partners",L21))),(MID(L21,FIND("Bay Partners",L21),FIND(",",L21,FIND("Bay Partners",L21)-1)-FIND("Bay Partners",L21))))
Currently, my formula can account for edge cases 2 and 3, however, I am having trouble creating a statement that can account for edge case 1, where there are no delimiters to search for at the end of the text.
Case 2 = Find when word is in the beginning. I search to see if the Find function returns 1, if so then I can account for when the name is in the beginning
Case 3 = Find when word in the middle. Searches for the comma. and if Find is not 1 I do -1 in the find to eliminate the , when it displays the text.
Case 1 = Find when word is at the end. I cannot figure this part out since there are no indications of the end.
I want to keep the data the way it is. This formula will be used across many more spreadsheets.
Thank you in advance for your help!!