Hi,

If formula compare exact matches. Can we make it to read true if there is approximate matching ?

here,   "A Conventional PCI bus add-in card connector"    "A PCI Conventional bus add-in card connector" match should be true not false. is it possible ?

It is possible - with a lot of different approaches that may or may not work.

The easiest would be to get rid of the leading letter and space from both lists, which would find matches for a few more, like the last item from the first list: DIMM 1...

The other would be to use a macro to replace common mis-phrasings in your data: convert, for example change "PCI Conventional bus" to  "Conventional PCI bus"

• Edited by Bernie Deitrick, Excel MVP 2000-2010Microsoft community contributor 20 hours 58 minutes ago

Hi Bernie Deitrick,

I am looking if there is any way to read a word or words, in a cell or group of cells, by any formula regardless of it's possition in a sentence. These words can be used for interpretation. Is it possible ? I do not want to correct orientation in the cell.

Thank you.

• Edited by drsantoshsinghrathore 9 hours 59 minutes ago

You could look for each word: copy your table to another column and use Text to columns...   using a space as the delimiter, and check for each of the reuslting words individually.

You can also extract the words using formulas like this, for a sentence in B2, copied to the right and then down.

=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(" "&$B2&REPT(" ",6)," ",REPT(CHAR(32),LEN($B2)+6),COLUMN(B1)),LEN($B2)+6))," ",REPT(CHAR(32),LEN($B2))),LEN($B2)))

• Edited by Bernie Deitrick, Excel MVP 2000-2010Microsoft community contributor 17 hours 37 minutes ago

It is possible - with a lot of different approaches that may or may not work.

The easiest would be to get rid of the leading letter and space from both lists, which would find matches for a few more, like the last item from the first list: DIMM 1...

The other would be to use a macro to replace common mis-phrasings in your data: convert, for example change "PCI Conventional bus" to  "Conventional PCI bus"

• Edited by Bernie Deitrick, Excel MVP 2000-2010Microsoft community contributor Tuesday, February 17, 2015 2:54 PM

Hi Bernie Deitrick,

I am looking if there is any way to read a word or words, in a cell or group of cells, by any formula regardless of it's possition in a sentence. These words can be used for interpretation. Is it possible ? I do not want to correct orientation in the cell.

Thank you.

• Edited by drsantoshsinghrathore Wednesday, February 18, 2015 1:52 AM

Thank you Bernie Deitrick,

It looks a difficult though. Actually I want formula to compare few of words in two cells or group of cell. And then give result true if it is so. This formula compares only first word. If it is " " it shows result " ".

You could look for each word: copy your table to another column and use Text to columns...   using a space as the delimiter, and check for each of the reuslting words individually.

You can also extract the words using formulas like this, for a sentence in B2, copied to the right and then down.

=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(" "&$B2&REPT(" ",6)," ",REPT(CHAR(32),LEN($B2)+6),COLUMN(B1)),LEN($B2)+6))," ",REPT(CHAR(32),LEN($B2))),LEN($B2)))

• Edited by Bernie Deitrick, Excel MVP 2000-2010Microsoft community contributor Tuesday, February 17, 2015 6:14 PM

Hi,

According to your description, your request may be done via Microsoft Fuzzy Lookup Addin. It can be used to identify fuzzy duplicate rows within a single table or to fuzzy join similar rows between two different tables.

Then, we also may be use macros via VBA code. If you have further question about coding, I recommend you post the question to VBA forum:

https://social.technet.microsoft.com/Forums/en-US/3792bc36-6aae-4d38-af53-72b337f64faf/error-using-byval-vba?forum=officeitpro

Regards, 

George Zhao
TechNet Community Support

Hi George Zhao,
Thank you for your reply. I do not want any add in. I used once and find my excel in lot off trouble. I had to reinstall MS OFFICE. Any formula or Code ?
regards
santosh