Excel Formula to identify similar text

-
Good day,

I am looking for help with an excel formula to identify similar text within two columns.

In column A there will be a specific word within a long description that needs to match all other records with the same text in column B. With this we just used True/False in the result column as an example.

For example take the word “Recorder” within the long description “Voice Recorder Sony ICD-PX720” in column A AND match it with all the records in Column B that has the word “Recorder” within the long text.

Column A _Column B _Result
AMPED FIVE_ Audio Receiver Oryx_TRUE
Audio Receiver_ Voice Recorder Sony ICD-PX720 _ TRUE
AUDIO Recorder_ Voice Recorder Phillips DVT2510_ TRUE
AUDIO VIDEO RECORDER _ Video Camera Sony Digital_ TRUE
BINOCULARS_ Main structure_ FALSE
BINOCULARS FUJINON_ Roof_ FALSE
BODY TRANSMITTER_ Internal Finishes _ FALSE
CAMERA CANON_ Erven 799_ FALSE
CAMERA NIKON_ House erven 802_ FALSE
CAMERA SECURITY_ Erven 807_ FALSE
CAMERA SONY_ House erven 842_ FALSE

Thank you in advance for taking the time to help and for your answers - this is much appreciated.

Regards

Reinette Gerber
See more 

2 replies

Posts
2501
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 16, 2019
356
0
Thank you
Hi Reinette,

What a messy question.
  • "Voice Recorder Sony ICD-PX720" is not in column A but B according to your sample data.
  • "AMPED FIVE_ Audio Receiver Oryx_TRUE " doesn't contain "Recorder" but is marked TRUE.
  • You want to look in column B, but this string "AUDIO VIDEO RECORDER _ Video Camera Sony Digital_ TRUE " shows Recorder in column A and gets the TRUE mark.


Find the word Recorder in column B and output TRUE or FALSE is what the following formula will do:
=IF(ISERROR(SEARCH("Recorder",B2)),"False","True")

Best regards,
Trowa
Gerberreinette
Posts
11
Registration date
Friday March 4, 2016
Status
Member
Last seen
July 18, 2019
-
Hi Trowa
Sorry if the question is messy. If you have a list of words in Column B how do you identify that specific words in Column A.

Thank you for the help.
Regard
TrowaD
Posts
2501
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 16, 2019
356 -
Hi Reinette,

You didn't address the messiness/confusion. If I can't tell how you got the result, then I can't get Excel to show the correct result either.

Best regards,
Trowa
Respond to TrowaD
Posts
11
Registration date
Friday March 4, 2016
Status
Member
Last seen
July 18, 2019
0
Thank you
Hi TrowaD

If you have the word "Tree" in column A, I want to find that same word in column B.
If the word is in Column A and B the formula must give me a result.... for example True
If it is only in one column it should give me a result.... for example False.

Or even if part of a word can be found in both columns the formula should say True.
For example in column A is "Tree" and in column B is "Tree House" it should also give me the result True.

Hope this is clear.

Thank you for your help I appreciate it.
Respond to Gerberreinette