Match substrings within cells

[YOUSSEFam]

Can anyone help me with an excel formula to look whether one cell with a string of text is the same compare to other cell with the same string of text but in different position/placement.

please see below for example:

cell A1: apple orange grape

cell A2: orange grape apple

I need a formula to check if A1 is the same or not to A2

thanks

[Pepe Le Mokko]

Hi
you posted in an Access subforum. Is this correct?

[6StringJazzer]

Let's just assume that was a newbie error; moved to a more appropriate subforum.

[Glenn Kennedy]

One way:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(" "&$C$2," ",REPT(" ",100)),100*{1,2,3},100)),E2)))=3,"Match","")

[Glenn Kennedy]

Hi, Youssef.

Forum Rule 1 is very short. It states: "Title must briefly summarize your request."

A GOOD thread title is the sort of thing you would use as a search term on Google*. Your chosen title would produce a squillion useless hits. In future, please take a bit more time to think of a descriptive title that would be "Google-friendly". Many people search the forum answers looking for help and your very general title wouldn't help them at all.

Since you are a new user of this site. On this occasion, I have changed it for you.

However, if you continue to use weak titles, you can expect to have your thread BLOCKED until you change it yourself. If that happens, to edit the thread title, open the original post, click "Edit Post" (bottom right) to access the area where you can edit your title.

*Other search engines are available!!!

Regards,

Glenn.

[YOUSSEFam]

Thank you for your feedback, I'm just worth knowing the number 100 indicated on the equation and how we can solve the problem indicated in red in attachment on the image remains of Match if we add a letter on one of the words

[YOUSSEFam]

well noted dear Glenn i am sorry as you have i am a beginner on this network

[Glenn Kennedy]

It can be done, but the formula will be much longer. I'll look at it tomorrow.

[Fluff13]

Borrowing Glenn's formula, try
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(" "&TRIM(MID(SUBSTITUTE(" "&$C$2," ",REPT(" ",100)),100*{1,2,3},100))&" "," "&E2&" ")))=3,"Match","")

[YOUSSEFam]

Hello Dean
Here is the problem in attachment

[YOUSSEFam]

1609537579846_image.png

[AliGW]

Change all the commas , to semi-colons ; .

Who is Dean?

[Fluff13]

You may need to replace the commas with semi-colons.

[Glenn Kennedy]

Fluff's amendment to my formula works fine. However, you'll need to use the semicolons in Morocco.

[YOUSSEFam]

Fluff's amendment to my formula works fine. However, you'll need to use the semicolons in Morocco.

Thanks it works

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.