To return any matched text or numbers.

Alice_Wong

New Member
Joined
Sep 13, 2006
Messages
30
Hi all,

I am working on some data in excel sheet. I need to compare two cells (1 cell and 1 column) in order to get any of the text or numbers that are matched.

For e.g. Cell A1 reads "Apple Pie", I would like to compare Cell A1 with the data i have in Column B. For instance Cell B3 now reads "Pie". So the text that is matched for Cell A1 and Cell B3 is "Pie".
What is the formula that I can use so that I can get the output as "Pie"?

Please help.

Thanks!
Alice[/quote]
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
If B3 is "Apple Tart", I would expect the result to be "Apple".
If B3 is "Plum Pie", I would expect the result to be "Pie".

Thanks! :)
 
Upvote 0
And what reslt would you expect if
A1 = Apple Pie
B3 = Plum Pie
B4 = Apple Tart
B5 = Apple and Plum Pie
 
Upvote 0
Column B will have only one of the below:-
Either Plum Pie or Apple Tart. I mean the data in Column B that I have will be very 'clean', my problem is how to make Column A's data to become more 'clean'.

E.g.
A1 = Apple Pie
B1 = Plum
B2 = Apple
B3 = Orange

I would not have "Pie" and "Apple" in Column B under seperate cells, the chances is very low.

So the result I expect is "Apple".

Below are some examples of my data:-
____________________________________________________________

A1 = CELETA/TOR/LUCENT
A2 = LUCENT/SUN
A3 = SOU/LUCENT
A4 = SEAGATE TECHNOLOGY
A5 = LECTRA/BOSE
A6 = SOURCE /HP
A7 = HVC/PALM

B1 = BOSE
B2 = HP
B3 = SEAGATE
B4 = PALM
B5 = LUCENT

Expected Results for:-
A1 = LUCENT
A2 = LUCENT
A3 = LUCENT
A4 = SEAGATE
A5 = BOSE
A6 = HP
A7 = PALM ____________________________________________________________

Please help.

Thanks,
Alice
 
Upvote 0
In C1 type:

=INDEX(B$1:B$5,MATCH(TRUE,ISNUMBER(SEARCH(B$1:B$5,A1)),FALSE))

and press Ctrl+Shift+Enter, not just Enter. If correctly entered Excel will surround this formula with curly braces {}.

Copy the formula to C2:C7.
 
Upvote 0

Forum statistics

Threads
1,218,746
Messages
6,144,265
Members
450,533
Latest member
xoxo1998

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