Excel Formula to identify similar text

Closed
Reinette - Updated on Jul 11, 2019 at 06:21 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Aug 6, 2019 at 11:42 AM
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
Related:

2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jul 11, 2019 at 12:14 PM
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 14 Registration date Friday March 4, 2016 Status Member Last seen July 31, 2023 2
Jul 12, 2019 at 03:29 AM
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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Updated on Jul 16, 2019 at 11:25 AM
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
Gerberreinette Posts 14 Registration date Friday March 4, 2016 Status Member Last seen July 31, 2023 2
Jul 18, 2019 at 08:57 AM
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.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Aug 6, 2019 at 11:42 AM
Hi Reinette,

Then I was pretty close already, just use the same formula for column A and then combine the 2:
=IF(AND(ISERROR(SEARCH("Tree",A1))=FALSE,ISERROR(SEARCH("Tree",B1))=FALSE),"True","False")
So if both searches don't produce an error, tree must have been found in both columns, thus the result is true.

Best regards,
Trowa