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

New Member
Joined
Apr 18, 2019
Messages
18
Office Version
  1. 2021
Platform
  1. Windows
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.

1739995371705.png


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.
 
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.
 
Upvote 0
.. 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.
 
Upvote 0
You could put the search term in another cell (say B2) and use
Excel Formula:
=MID(A2,SEARCH(" "&B2,A2)+1,100)
 
Upvote 0
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.
Excel Formula:
=MID(A2,SEARCH(" "&B2&" ",A2)+1,100)
or
Excel Formula:
=REPLACE(A2,1,SEARCH(" "&B2&" ",A2),"")
 
Upvote 0

Forum statistics

Threads
1,226,873
Messages
6,193,450
Members
453,800
Latest member
dmwass57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top