# Compare two cell if test exist in any order

#### MissMiniExcel

##### New Member
Greetings from snowing Colorado.

Case -
Cell 1 I love winter time especially when skiing.
Cell 2 I especially love winter when time skiing.

The same text exist in Cell 1 and Cell 2.

What formula or vba will solution case?

MissMiniExcel

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### AliGW

##### Banned
You are going to have to be much more precise in your requirements! What EXACTLY do you mean by "the same text"? Is it just one word ("skiing")? Will you be searching specifically for this word or just trying to find strings containing one or more matching words?

#### MissMiniExcel

##### New Member
Hello AliGGW,

Thanks for your response and sharing your knowledge.

The same words exist in both cells, however they are not in the same sequence.

Example, sentence has 6 words, but not in same sequence.
cell 1: I love the first sight of snow
cell 2: I love sight the snow of first

You are going to have to be much more precise in your requirements! What EXACTLY do you mean by "the same text"? Is it just one word ("skiing")? Will you be searching specifically for this word or just trying to find strings containing one or more matching words?

Thanks,
Myrna

#### AliGW

##### Banned
So all of the words in the string have to appear in both cells - yes?

#### Tetra201

##### MrExcel MVP
@MissMiniExcel:

See if the following UDF (User Defined Function) works for you.
Example of usage: =CmpWrd(A1,A2); it returns TRUE or FALSE.
Code:
``````Function CmpWrd(ByVal S1, S2 As String) As Boolean
Dim S(1 To 2) As String
S(1) = S1: S(2) = S2
For i = 1 To 2 Step 1
For Each c In Array(".", ",", ":", ";""'", """", "-", "_", "/", "\", "(", ")", "!", "?")
S(i) = Replace(S(i), c, "")
Next c
S(i) = UCase(WorksheetFunction.Trim(S(i)))
Next i
CmpWrd = True
A1 = Split(S(1), " "): A2 = Split(S(2), " ")
If UBound(A1) <> UBound(A2) Then CmpWrd = False: Exit Function
For i = LBound(A1) To UBound(A1) Step 1
wdFound = False
For j = LBound(A2) To UBound(A2) Step 1
If A1(i) = A2(j) Then wdFound = True: Exit For
Next j
If Not wdFound Then CmpWrd = False: Exit Function
Next i
End Function``````

#### MissMiniExcel

##### New Member
Yes, all words have to appear in both cells.

The words do not have to appear in the same other.

I am going to try Tetra201 code.

Keep you posted on the results.

Thanks a tons for your help!
Myrna

So all of the words in the string have to appear in both cells - yes?

#### MissMiniExcel

##### New Member
Hello Tetra,

Yes, the User Defined Function worked flawlessley.

Thank you for sharing your knowledge.

MissMiniExcel

@MissMiniExcel:

See if the following UDF (User Defined Function) works for you.
Example of usage: =CmpWrd(A1,A2); it returns TRUE or FALSE.
Code:
``````Function CmpWrd(ByVal S1, S2 As String) As Boolean
Dim S(1 To 2) As String
S(1) = S1: S(2) = S2
For i = 1 To 2 Step 1
For Each c In Array(".", ",", ":", ";""'", """", "-", "_", "/", "\", "(", ")", "!", "?")
S(i) = Replace(S(i), c, "")
Next c
S(i) = UCase(WorksheetFunction.Trim(S(i)))
Next i
CmpWrd = True
A1 = Split(S(1), " "): A2 = Split(S(2), " ")
If UBound(A1) <> UBound(A2) Then CmpWrd = False: Exit Function
For i = LBound(A1) To UBound(A1) Step 1
wdFound = False
For j = LBound(A2) To UBound(A2) Step 1
If A1(i) = A2(j) Then wdFound = True: Exit For
Next j
If Not wdFound Then CmpWrd = False: Exit Function
Next i
End Function``````

#### MissMiniExcel

##### New Member
AliGW,

Tetra's suggested Excel Function worked!

Thanks again for helping.

Have a fab week,
MissMiniExcel

#### Rick Rothstein

##### MrExcel MVP
I have a different approach for your problem. I created a function which sorts all of the words in the text passed to it and returns a comma-delimited list (the delimiter is easily changed in the next-to-last line of code) which I thought could possibly be useful in other applications one may create in the future. To use my function, simply compare the output from it for the two cells you want to check. For example...

=SortWords(A1)=SortWords(B1)

Here is the code for my function...
Code:
``````Function SortWords(ByVal Text As String) As String
Dim X As Long, Data() As String
Text = Replace(Text, " - ", " ")
For X = 1 To Len(Text)
If Mid(Text, X, 1) Like "[!A-Za-z0-9-]" Then Mid(Text, X) = " "
Next
Data = Split(Application.Trim(Text), " ")
With CreateObject("System.Collections.ArrayList")
For X = 0 To UBound(Data)
Next
.Sort
SortWords = Join(.ToArray, ", ")
End With
End Function``````

#### Tetra201

##### MrExcel MVP
Hello Tetra,
Yes, the User Defined Function worked flawlessley.
Thank you for sharing your knowledge.
MissMiniExcel
You are welcome.

Replies
3
Views
522
Replies
1
Views
239
Replies
4
Views
779
Replies
4
Views
1K
Replies
3
Views
723

1,191,696
Messages
5,988,155
Members
440,131
Latest member
EricMoz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back