# To return any matched text or numbers.

#### Alice_Wong

##### New Member
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"?

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.
What results would you expect if B3 contained "Apple Tart" or "Plum Pie"?

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!

And what reslt would you expect if
A1 = Apple Pie
B3 = Plum Pie
B4 = Apple Tart
B5 = Apple and Plum Pie

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 ____________________________________________________________

Thanks,
Alice

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.

Thank you! It works now

Have a nice day!

Replies
5
Views
517
Replies
8
Views
97
Replies
4
Views
230
Replies
6
Views
681
Replies
3
Views
104

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.

### Which adblocker are you using?

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

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