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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Alice_Wong

New Member
Joined
Sep 13, 2006
Messages
30
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! :)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,494
Office Version
  1. 365
Platform
  1. Windows
And what reslt would you expect if
A1 = Apple Pie
B3 = Plum Pie
B4 = Apple Tart
B5 = Apple and Plum Pie
 

Alice_Wong

New Member
Joined
Sep 13, 2006
Messages
30

ADVERTISEMENT

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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Forum statistics

Threads
1,136,993
Messages
5,679,023
Members
419,800
Latest member
spvsr999

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
Top