Extract text within a cell

New Contributor
Hello,

I’ve been looking around and I’m really struggling to find a solution to this. I have multiple rows of cells that I am trying to create a formula to extract text within an cell.

Here is an example:
FOUND_video06s-connect-stories

So I need to extract “video06s-connect”, is this possible? “FOUND_” will always be there, and there will always be a dash (-) between that last word(stories in this case) and second to last word(connect in this case)

Any help is appreciated.
3 Replies

@Cmesquita 

Let's say that you have such a text string in A2.

The following formula will extract the text you want:

 

=MID(A2,7,FIND("@",SUBSTITUTE(A2,"-","@",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-7)

 

In this formula, @ is just a character that doesn't occur in the text string. If your text string might contain @, simply replace @ with a character that will not occur, for example |

Ok, that worked perfectly, is there a way to extract that same text if there is more before the “found_” in different situations? If not totally fine, this is a huge help!

@Cmesquita 

That would be

 

=MID(A2,FIND("FOUND_",A2)+6,FIND("@",SUBSTITUTE(A2,"-","@",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-FIND("FOUND_",A2)-6)

 

If FOUND_ could be in upper case or lower case (or mixed case), use

 

=MID(A2,SEARCH("FOUND_",A2)+6,FIND("@",SUBSTITUTE(A2,"-","@",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-SEARCH("FOUND_",A2)-6)