Excel formula to return whats common in two text strings.

Jamel

Board Regular
Joined
Mar 2, 2010
Messages
54
Hello,

I'm looking for a way (excel formula) to compare two text strings and return what's in common between the two.

All the information is in one column so i will apply the formula in the column next to it and copy the formula down to the other rows.

M T
M T W
T W H

The answer is T

Any help would be appreciated




<colgroup><col width="237" style="width:178pt"> </colgroup><tbody>
</tbody>
 

Coding4Fun

Active Member
Joined
Feb 16, 2017
Messages
344
The question is not clear nor is the answer. You mention "I'm looking for a way (excel formula) to compare two text strings and return what's in common between the two." however you are saying T is the answer.

I see Row 1 and Row 2 have M & T in common. Row 2 and 3 have T & W in common.

Rows 1, 2 & 3 all have T in common. Looking to compare 2 or 3 text strings?

Also are you open to VBA or does it have to be a Formula?
 
Last edited:

Jamel

Board Regular
Joined
Mar 2, 2010
Messages
54
First and foremost. Thanks for your reply.

Now for the confusion. I was attempting to show the comparison of two cells and i also included the answer which is where the confusion kick in.

Im also often to vb but I'd thought it be too difficult because the source im using to apply the request formula points at a pivot table because the data is constantly changing. The pivot references 21,000 records. Rows of records. I knew the vb route would be more labor intensive so i was trying to not bother anyone that much.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,273
Office Version
365
Platform
Windows
I was attempting to show the comparison of two cells and i also included the answer which is where the confusion kick in.
To me, the confusion is not because you included the answer (blue) but because you say compare two cells when you have given three (red).


M T
M T W
T W H

The answer is T

<colgroup><col width="237" style="width:178pt"> </colgroup><tbody>
</tbody>
 

Jamel

Board Regular
Joined
Mar 2, 2010
Messages
54
I'm not sure how those colors were included.
I just typed the text. I didn't color code it. I see how that can be confusing
 

Jamel

Board Regular
Joined
Mar 2, 2010
Messages
54
Also what's obvious is the latest mistake on my part. I did mistakenly include three. My original question was comparing two. I Google it and couldn't find an existing formula that worked. And when i asked the question going a bit further. My mistake
 

Jamel

Board Regular
Joined
Mar 2, 2010
Messages
54
No suggestions?
 

Coding4Fun

Active Member
Joined
Feb 16, 2017
Messages
344
Your question is still not clear but I think think is what you are looking for. Put your list in Column A, then put this code in a standard module and run it.

It will step through each Row of Column A and compare the Characters within each row to those in the next row. Each time it find a match it will output this in column B next to the "Top Row" in the comparison.

Example Table - Start in A, Resulting output in Column B
AB
M TM T
M T WT W
T W HT
K A TK A T
A K TT
T W HT W
W A P T

<tbody>
</tbody>

Code:
Sub Test()


myRange = Cells(Rows.Count, "A").End(xlUp).Row


For e = 1 To myRange ' Loop through each Row


    For x = 1 To Len(Range("A" & e).Value) ' Loop through checking each Char vs Next Row
    
        checkVal1 = Mid(Range("A" & e).Value, x, 1)
    
        For y = 1 To Len(Range("A" & e + 1).Value) ' Loop through next Row Chars
        
            checkVal2 = Mid(Range("A" & e + 1).Value, y, 1)
            
            If checkVal1 = checkVal2 Then
            
            Range("B" & e).Value = Trim(Range("B" & e).Value) & " " & checkVal1


            End If
                
        Next y
    
    Next x


Next e


End Sub
 
Last edited:

Coding4Fun

Active Member
Joined
Feb 16, 2017
Messages
344
Example Table - Start in A, Resulting output in Column B
AB
M TM T
M T WT W
T W HT
K A TK A T
A K TT
T W HT W
W A P T

<tbody>
</tbody>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,273
Office Version
365
Platform
Windows
Also what's obvious is the latest mistake on my part. I did mistakenly include three.
That is what Coding4Fun & I were trying to point out all along. ;)

I'm not sure how those colors were included.
I included the colour to try to highlight the above point. :)

I would suggest a user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Code:
Function Common(s1 As String, s2 As String) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[^" & Replace(s1, " ", "") & "]"
    Common = Application.Trim(.Replace(s2, " "))
  End With
End Function
I have assumed that your sample data is representative in that
- each string consists of single characters separated by a standard space character.
- there are no repeat characters in any of the strings **

Excel Workbook
AB
1DataCommon
2M T
3M T WT W
4T W HT
5K A TA K T
6A K TT
7T W HW T
8W A P T
Sheet2



** If my assumption about no repeat characters is not correct, then what should be the result for this pair of strings?
Q R Z R Q
Q Q R R

or this pair?
Z
Z W W Z
 

Forum statistics

Threads
1,082,309
Messages
5,364,424
Members
400,801
Latest member
julievandermeulen

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top