aghaffar82
Board Regular
- Joined
- Jun 13, 2019
- Messages
- 65
- Office Version
- 365
- Platform
- Windows
Hello,
I have this string in cell O1201 and formula in cell U1201
String:
Attorney/ownerLaw Office of Mark A. SkeltonCurrentLaw Office of Mark A. Skelton has served East Tennessee since 1983. The attorneys with the firm include Mark Albert Skelton and Amy Kathleen Skelton.
Formula:
=IFERROR(IF(IFERROR(SEARCH(B1201,O1201),IFERROR(SEARCH(LEFT(B1201,(FIND(" ",B1201,1)-1)),O1201),IFERROR(SEARCH(TRIM(RIGHT(SUBSTITUTE(B1201," ",REPT(" ",100)),100)),O1201),"")))<SEARCH("Current",O1201),"Yes","No"),"N/A")
Text in cell B1201:
Mark Albert Skelton
The formula is resulting in a "No", I want it to be a "Yes", the reason it is returning "No" is understood that it is finding an exact match of the value of B1201 after the word Current being the first condition, but I just want it to stay within the string before the word "Current" and if it doesn't find an exact match of the value in B1201 before the word Current, it should then look for first name and last name parts but also before the word Current.
Hope it makes sense, happy to clarify it further if needed. And thank you in advance for your kind consideration.
Best Regards
I have this string in cell O1201 and formula in cell U1201
String:
Attorney/ownerLaw Office of Mark A. SkeltonCurrentLaw Office of Mark A. Skelton has served East Tennessee since 1983. The attorneys with the firm include Mark Albert Skelton and Amy Kathleen Skelton.
Formula:
=IFERROR(IF(IFERROR(SEARCH(B1201,O1201),IFERROR(SEARCH(LEFT(B1201,(FIND(" ",B1201,1)-1)),O1201),IFERROR(SEARCH(TRIM(RIGHT(SUBSTITUTE(B1201," ",REPT(" ",100)),100)),O1201),"")))<SEARCH("Current",O1201),"Yes","No"),"N/A")
Text in cell B1201:
Mark Albert Skelton
The formula is resulting in a "No", I want it to be a "Yes", the reason it is returning "No" is understood that it is finding an exact match of the value of B1201 after the word Current being the first condition, but I just want it to stay within the string before the word "Current" and if it doesn't find an exact match of the value in B1201 before the word Current, it should then look for first name and last name parts but also before the word Current.
Hope it makes sense, happy to clarify it further if needed. And thank you in advance for your kind consideration.
Best Regards