• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help extracting substrings from cell text with 3 edge cases

J

JuliusV

Member
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!! :)
 
Hi, JuliusV!

As a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, try this:
=IZQUIERDA(REEMPLAZAR(L21;1;HALLAR("Bay Partners";L21)-1;"")&",";HALLAR(",";REEMPLAZAR(L21;1;HALLAR("Bay Partners";L21)-1;"")&",")-1) -----> in english: =LEFT(REPLACE(L21,1,SEARCH("Bay Partners",L21)-1,"")&",",SEARCH(",",REPLACE(L21,1,SEARCH("Bay Partners",L21)-1,"")&",")-1)

Regards!
 
Hi ,

I am not sure what exactly you want the formula to do , but surely a simple one should do :

=IF(ISERROR(SEARCH("Bay Partners X",L21)),"","Bay Partners X")

unless the X stands for more text which can vary. Can you clarify ?

Narayan
 
SirJB7 - Thank you for your post. I believe it satisifes al lthe conditions, but I am still testing it across all spreadsheets and confirm later today.

Narayan - For Bay Partners X, the X can represent any varying length of text i.e. (Bay Partners Cash Fund, Bay Partners 13, ect.)

My formula currently can identify when the word is in the beginning (i.e Bay Partners Cash Fund, Another Fund)
This is what If(FIND("Bay Partners",L21) = 1,(MID(L21,FIND("Bay Partners",L21),FIND(",",L21,FIND("Bay Partners",L21))-FIND("Bay Partners",L21))) addresses

Also, my formula found words in the middle (i.e. Another Fund, Bay Partners 15, Another Fund)
This is what (MID(L21,FIND("Bay Partners",L21),FIND(",",L21,FIND("Bay Partners",L21)-1)-FIND("Bay Partners",L21))))

What I was looking for was a way to find when words were at the end (i.e. Another Fund, Bay Partners Fast Fund)
The issue I ran into was identifying the end.

I was hoping to find a solution with what I currently have, but if that is not possible that is fine. I will investigate all the solutions provided.
 
This works with your current posted info.
=TRIM(LEFT(SUBSTITUTE(MID(L21,SEARCH("Bay Partners",L21,1),99)&",",",",REPT(" ",99)),99))
 
Shrivallabha - I tried using the code you provided, but it seems not to work for me. I may not be putting it correctly. I really appreciate the help! Thank you.
 
I believe SIRBJ7's code covers all three edge cases. I have applied it across 7 other spreadsheets and everything seems to be correct.

Can someone please help dissect how the function works? I used F9 to break it apart and read through the function descriptions multiple times, but am still confused about how it works. I think the first part of the left - always adds a comma at the end, but am not clear how it is able to do that properly or for the cases where there are already commas, how does it know not to add it.
 
Hi, JuliusV!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

And about the biology experiment here it goes:

Formula: =LEFT(REPLACE(L21,1,SEARCH("Bay Partners",L21)-1,"")&",",SEARCH(",",REPLACE(L21,1,SEARCH("Bay Partners",L21)-1,"")&",")-1)

Instead of working directly on cell L21 works on this:
REPLACE(L21,1,SEARCH("Bay Partners",L21)-1,"")&","
that's equal to remove everything before the "Bay Partners", by replacing from 1st char of L21 to the previous char where BP it's found with null string ""; and appending a last comma for equaling edge conditions with other cases.

So starting that string with BP and surely ending with a comma, it's just a matter of extracting the substring until the previous character to the comma:
=LEFT(<previous_substring>,SEARCH(",",<previous_substring>)-1)

Regards!
 
JuliusV said:
Shrivallabha - I tried using the code you provided, but it seems not to work for me. I may not be putting it correctly. I really appreciate the help! Thank you.
Click to expand...
You already have a working solution which is great.

I had tested it here on your posted data. I am attaching the example sheet. The results are correct for the sample provided at least ;)
 

Attachments

  • Example Implementation.xlsx
    9.2 KB · Views: 7
Shirvallabha - Thanks for uploading the implementation. I thought that I was supposed to add your part of the forumla to my current formula. Sorry for the confusion.

I was wondering can you please explain your whole function. I am especially confused about this part:
",",",",REPT(" ",99)),99))
I have never used REPT or Substitute. Also, why is it necessary to TRIM?
 
Hi, JuliusV!
Just as a tip, remember that if you enter in an empty cell "=XXX(" (unquoted) and press the "fx" button at the left of the edition bar text box, you'll be prompted with the function wizard which shows every argument, gives a brief description of the function and by the bottom left link lets you access the built-in or online help. It works for any function.
The same from the VBA editor, you can place the cursor (keyboard, so click, don't hover with mouse) on any VBA reserved word and press F1 to access the same type of help.
Regards!
 
Hi Julius,

Your example has two cases where we had comma and one without comma. So we added a comma so all cases in principle started looking the same. Following portion retains all data in the entry starting from keyword "Bay Partners"
MID(L21,SEARCH("Bay Partners",L21,1),99)

Once done we add a comma to it so that all cases become pretty similar as below:
MID(L21,SEARCH("Bay Partners",L21,1),99)&","

The SUBSTITUTE function is quite versatile when it comes down to string parsing. Lets say the mid formula is X then the SUBSTITUTE portion looks like
SUBSTITUTE(X&",",",",REPT(" ",99)
So we replace "," (comma) in the source string with 99 spaces. so ultimately we get a result string like below:
Bay Partners X + 99 Spaces + Whatever that was on the right side of comma.

Now we use LEFT function to cut down things assuming result from SUBSTITUTE as Y.
=LFET(Y,99)
we get
Bay Partners X + (99 - Length Bay partners X string) number of spaces.

Then TRIM function simply cuts all the spaces on the right hand side of desired result.

If you use formula evaluation tool from Formula tab on the ribbon and try to step through function then it will become clearer for you to understand.
 
You must log in or register to reply here.
Back
Top