Matching Digits in Range

[hrayani]

Hello To All,

I need a formula which can show matching digits in a range of cells (for example A1:b1

For example

A1 = 123456
B1 = 456789

Answer should be (456)

Regards,

Humayun

 

[Peter_SSs]

Try this UDF. Post back if you need help with how to implement it.

<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> MatchChar(s1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, s2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)<br>    <SPAN style="color:#00007F">Static</SPAN> RegEx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <br>    <SPAN style="color:#00007F">Dim</SPAN> p <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> RegEx <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> RegEx = CreateObject("VBScript.RegExp")<br>        RegEx.Global = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>    <SPAN style="color:#00007F">With</SPAN> RegEx<br>        .Pattern = "(.)"<br>        p = .Replace(s1, "$1|")<br>        p = Left(p, Len(p) - 1)<br>        .Pattern = "[^" & p & "]"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    MatchChar = RegEx.Replace(s2, "")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><br></FONT>


Formula in C1 copied down.

Excel Workbook
	A	B	C
1	123456	456789	456
2	xyz	123
3	765432	234567	234567
4	abcde123	2fgckeh	2ce
Match Characters

 

[Aladin Akyurek]

Hello To All,

I need a formula which can show matching digits in a range of cells (for example A1:b1

For example

A1 = 123456
B1 = 456789

Answer should be (456)

Regards,

Humayun

Add the following function codes as a module to you workbook...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

and now invoke:

C1, control+shift+enter, not just enter:

=ACONCAT(IF(ISNUMBER(FIND(V(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),B1)),V(),""))