Excel Formula to identify similar text

Closed
Reinette - Updated on Jul 11, 2019 at 06:21 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 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

2 replies

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
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
0
Gerberreinette Posts 13 Registration date Friday March 4, 2016 Status Member Last seen July 6, 2021
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
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
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
0
Gerberreinette Posts 13 Registration date Friday March 4, 2016 Status Member Last seen July 6, 2021
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.
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
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
0