Search multiple text strings in a single cell and return found text

kay jay

New Member
Joined
Mar 20, 2013
Messages
3
First post and I'm very excited about it - I am trying to search multiple text strings in a single cell and return the string if found. Is this possible? I have tried to nest if, mid and find statements but am not getting results past the first if(mid(find statement. The cell I am searching could contain "YTD AAA KNI", I want to search it for "AAA" or "BBB" and in this case return the "AAA". Thanks!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi

Is there a possibility there may be more than one match within the single cell (so both "AAA" and "BBB") - in which case which would you want returned? I presume there are many possibile texts that could be present in the cell and you have a list you want to match against?
 
Upvote 0
Hi

Welcome to the Forum.

If the "AAA"/"BBB" and "AAA" is no longer that 10 characters and has one space either side try this :-
Code:
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",10)),FIND(" ",A1),20))

hth
 
Last edited:
Upvote 0
There could be no more than one match. Yes, many possible texts with a list to match against.
 
Upvote 0
See if this gives you what you want:


Excel 2010
ABCDEFG
1ListFormulaTable of values
2YTD AAA KNIAAAText To MatchText To Return
3YTD BBB KNIBBBAAAAAA
4Random Text. ZZZ More random textThe end of the alphabet!BBBBBB
5ZZZThe end of the alphabet!
6
Sheet2
Cell Formulas
RangeFormula
B2=LOOKUP(2^15,SEARCH($F$3:$F$5,A2),$G$3:$G$5)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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