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.
"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")
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.
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.
- Posts
- 11
- Registration date
- Friday March 4, 2016
- Status
- Member
- Last seen
- July 18, 2019
-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
- Posts
- 2519
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 3, 2019
361 -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