I need a formula to find a string in a string cell and extract that string and all the characters that come after it (I don't have TextBefore/After)

[Cozkincaud]

Hi,

I only have Office Professional 2021 so I can't use TEXTBEFORE or TEXTAFTER to extract text strings from string cells.

I need to find a string in a string cell and extract that string and all the characters that come after it. Here are some of the string cells I need to extract strings from. In these cells I want to extract sthe string "in" and every character after it.

In the case of the first cell ("Edward Waldegrave esquire in londes") I need to find the string "in", extract it along with all the characters that come after it (" londes"). The string I will be searching for will always be different.

I found plenty of formulas for extracting text strings after a specific character, but none for specific strings, or at least none that I can get to work (mid/right with find/search). Note: I need to extract the search string (e.g. "in" as in "in lands" in the cell string, "Edward Waldegrave esquire in londes") as well as all the text that comes after it.

My thanks in advance for your advice and suggestions.

 

[Fluff]

How about

=MID(A2,SEARCH(" in",A2)+1,100)

 

[Cozkincaud]

Thanks for the suggestion. It will work, but it's not what I need. As I wrote in my post, the text I will be searching for changes. In this case I can use "in". In another case I might need to use "to". My mistake, I shouldn't have used the same example in all the cells.

 

[Peter_SSs]

the text I will be searching for changes.

.. so how will we know what text you will be searching for in each row given that your only example was searching for "in"?

 

[Cozkincaud]

.. so how will we know what text you will be searching for in each row given that your only example was searching for "in"?

I will know because there are common themes running through the texts I am analysing. In this case I can use the text "in' to extract the text I need. In another case it might be "by yere" and in yet another case the text could be "guild box". This is why I need a generic expression, not a specific one.

 

[Fluff]

You could put the search term in another cell (say B2) and use

=MID(A2,SEARCH(" "&B2,A2)+1,100)

 

[Peter_SSs]

I would recommend changing from SEARCH to FIND otherwise when looking for, say "in" with "Robert Ingram servaunt in wages" you would get "Ingram servaunt in wages" not just "in wages"

Probably safer still would be to include a space after as well as before.

=MID(A2,SEARCH(" "&B2&" ",A2)+1,100)

or

=REPLACE(A2,1,SEARCH(" "&B2&" ",A2),"")