Howdy,

I have two functions. SPACE_OVERLAP() finds common numbers between two cells with comma-delimited values like this:
A1: 1,2,3,4,5,6,7,8,9
A2: 4,5,8,10,12,13,14,15
B1: =SPACE_OVERLAP(A1,A2) = 4,5,8

And NUMCOMMA() turns a cell into a comma-delimited value, like this:
A1: 1-5
A2: =NUMCOMMA(A1) = 1,2,3,4,5

The problem is that when I put NUMCOMMA() inside of SPACE_OVERLAP()
=SPACE_OVERLAP(A1,NUMCOMMA(A2))
I get a #VALUE error.

Space Overlap:
Option Explicit

Public Function SPACE_OVERLAP(ByRef Cell1 As Range, ByRef Cell2 As Range) As String
Dim ARR1() As String
Dim ARR2() As String
Dim sResult As String
Dim i As Integer
Dim n As Integer
    
ARR1 = Split(Cell1.Value, ",")
ARR2 = Split(Cell2.Value, ",")
 
For i = LBound(ARR1) To UBound(ARR1)
    For n = LBound(ARR2) To UBound(ARR2)
            If ARR1(i) = ARR2(n) Then
                    sResult = sResult & "," & ARR2(n)
            End If
    Next n
Next i

If Left(sResult, 1) = "," Then sResult = Right(sResult, Len(sResult) - 1)

SPACE_OVERLAP = sResult

End Function

Numcomma:

Public Function NUMCOMMA(ByRef Cell As Range) As String
Dim tmp As String
Dim ary As Variant
Dim pos As Long
Dim i As Long, ii As Long

    ary = Split(Cell.Value, ",")
    For i = LBound(ary) To UBound(ary)
    
        pos = InStr(ary(i), "-")
        If pos > 0 Then
        
            For ii = Left$(ary(i), pos - 1) To Mid$(ary(i), pos + 1, 99)
            
                tmp = tmp & ii & ","
            Next ii
        Else
            
            tmp = tmp & ary(i) & ","
        End If
    Next i
    
    NUMCOMMA = Left$(tmp, Len(tmp) - 1)
End Function