I have a set of digits in one cell separated by a comma and a bigger set or
master set of digits in the other cell. Is there a formula where I can
segregate the unmatching digits of the two cells onto the third cell

E.g,.

A1: 121, 211, 244
B1: 121, 211, 1314, 566, 667
What I want as result:
C1: 1314, 566, 667 (ideally B1 - A1)

Thanks

You need a custom function. Try the one below. I wrote a very simple method
of perfoming you request, but it has some limitations. If in the 2nd string
you have 13 it will match any number with 13 in it such as 135, 313,1013. if
this is a problem I can modify the function. this is just a simple solution.

Call with
=findtext(B1,A1)

Function findtext(Parm1 As String, Parm2 As String) As String
Dim NewNum As String
Dim ParseString As String

findtext = ""
ParseString = Parm1
Do While Len(ParseString) 0
CommaPos = InStr(ParseString, ",")
If CommaPos 0 Then
NewNum = Trim(Left(ParseString, CommaPos - 1))
ParseString = Trim(Mid(ParseString, CommaPos + 1))
Else
NewNum = Trim(ParseString)
ParseString = ""
End If

If InStr(Parm2, NewNum) = 0 Then
If Len(findtext) = 0 Then
findtext = NewNum
Else
findtext = findtext & ", " & NewNum
End If
End If
Loop


End Function

"Malik" wrote:

I have a set of digits in one cell separated by a comma and a bigger set or
master set of digits in the other cell. Is there a formula where I can
segregate the unmatching digits of the two cells onto the third cell

E.g,.

A1: 121, 211, 244
B1: 121, 211, 1314, 566, 667
What I want as result:
C1: 1314, 566, 667 (ideally B1 - A1)

Thanks

is this a macro u wrote? i m sorry i though there might be a simpler formula
that can be used. anyways thx as i dont pretty well understand macros.


"Joel" wrote:

You need a custom function. Try the one below. I wrote a very simple method
of perfoming you request, but it has some limitations. If in the 2nd string
you have 13 it will match any number with 13 in it such as 135, 313,1013. if
this is a problem I can modify the function. this is just a simple solution.

Call with
=findtext(B1,A1)

Function findtext(Parm1 As String, Parm2 As String) As String
Dim NewNum As String
Dim ParseString As String

findtext = ""
ParseString = Parm1
Do While Len(ParseString) 0
CommaPos = InStr(ParseString, ",")
If CommaPos 0 Then
NewNum = Trim(Left(ParseString, CommaPos - 1))
ParseString = Trim(Mid(ParseString, CommaPos + 1))
Else
NewNum = Trim(ParseString)
ParseString = ""
End If

If InStr(Parm2, NewNum) = 0 Then
If Len(findtext) = 0 Then
findtext = NewNum
Else
findtext = findtext & ", " & NewNum
End If
End If
Loop


End Function

"Malik" wrote:

I have a set of digits in one cell separated by a comma and a bigger set or
master set of digits in the other cell. Is there a formula where I can
segregate the unmatching digits of the two cells onto the third cell

E.g,.

A1: 121, 211, 244
B1: 121, 211, 1314, 566, 667
What I want as result:
C1: 1314, 566, 667 (ideally B1 - A1)

Thanks

This is a custom function, but it behaves the same as other workbook function
such as =Sum(A1:B5). If you right click the tabb on the bottom of the
worksheet normally (sheet1) and select view code. then go to VBA Menu -
Insert Module. Simply copy the macro from this posting (starts with
"function" and ends witth "end function").the in you worksheet enter
=findtext(B1,A1).

"Malik" wrote:

is this a macro u wrote? i m sorry i though there might be a simpler formula
that can be used. anyways thx as i dont pretty well understand macros.


"Joel" wrote:

You need a custom function. Try the one below. I wrote a very simple method
of perfoming you request, but it has some limitations. If in the 2nd string
you have 13 it will match any number with 13 in it such as 135, 313,1013. if
this is a problem I can modify the function. this is just a simple solution.

Call with
=findtext(B1,A1)

Function findtext(Parm1 As String, Parm2 As String) As String
Dim NewNum As String
Dim ParseString As String

findtext = ""
ParseString = Parm1
Do While Len(ParseString) 0
CommaPos = InStr(ParseString, ",")
If CommaPos 0 Then
NewNum = Trim(Left(ParseString, CommaPos - 1))
ParseString = Trim(Mid(ParseString, CommaPos + 1))
Else
NewNum = Trim(ParseString)
ParseString = ""
End If

If InStr(Parm2, NewNum) = 0 Then
If Len(findtext) = 0 Then
findtext = NewNum
Else
findtext = findtext & ", " & NewNum
End If
End If
Loop


End Function

"Malik" wrote:

I have a set of digits in one cell separated by a comma and a bigger set or
master set of digits in the other cell. Is there a formula where I can
segregate the unmatching digits of the two cells onto the third cell

E.g,.

A1: 121, 211, 244
B1: 121, 211, 1314, 566, 667
What I want as result:
C1: 1314, 566, 667 (ideally B1 - A1)

Thanks

thx jo..dat was cool...it serves my purpose..but cud u make me understand the
way it has worked..i mean what is module..shall i save this as a macro? and a
module, unlike macros are run when a formula is inserted?
i mean if u cud throw sum light that shall really be helpful..thx so much
again.....

"Joel" wrote:

This is a custom function, but it behaves the same as other workbook function
such as =Sum(A1:B5). If you right click the tabb on the bottom of the
worksheet normally (sheet1) and select view code. then go to VBA Menu -
Insert Module. Simply copy the macro from this posting (starts with
"function" and ends witth "end function").the in you worksheet enter
=findtext(B1,A1).

"Malik" wrote:

is this a macro u wrote? i m sorry i though there might be a simpler formula
that can be used. anyways thx as i dont pretty well understand macros.


"Joel" wrote:

You need a custom function. Try the one below. I wrote a very simple method
of perfoming you request, but it has some limitations. If in the 2nd string
you have 13 it will match any number with 13 in it such as 135, 313,1013. if
this is a problem I can modify the function. this is just a simple solution.

Call with
=findtext(B1,A1)

Function findtext(Parm1 As String, Parm2 As String) As String
Dim NewNum As String
Dim ParseString As String

findtext = ""
ParseString = Parm1
Do While Len(ParseString) 0
CommaPos = InStr(ParseString, ",")
If CommaPos 0 Then
NewNum = Trim(Left(ParseString, CommaPos - 1))
ParseString = Trim(Mid(ParseString, CommaPos + 1))
Else
NewNum = Trim(ParseString)
ParseString = ""
End If

If InStr(Parm2, NewNum) = 0 Then
If Len(findtext) = 0 Then
findtext = NewNum
Else
findtext = findtext & ", " & NewNum
End If
End If
Loop


End Function

"Malik" wrote:

I have a set of digits in one cell separated by a comma and a bigger set or
master set of digits in the other cell. Is there a formula where I can
segregate the unmatching digits of the two cells onto the third cell

E.g,.

A1: 121, 211, 244
B1: 121, 211, 1314, 566, 667
What I want as result:
C1: 1314, 566, 667 (ideally B1 - A1)

Thanks

Macro's are part of the workbook and automatically get saved when you save
the workbook. there are two flavors of macros which are Sub (subroutines)
and functions. Sub must manually be called and do not return a value. They
can modify the worksheets and cannot do just abbout anything you cna imagine.

Functions are very similar to Sub, and have some limitations. They behavve
just like otherr workbook functions and return a single value which Sub
cannot do.

In VBA there are Three different type pages. Modules, Thisworkbook, and
sheet pages (one for each sheet in the workbook). Modules are general
purposes. thisworkbook must be used for workbook events such as Open
Workbook and Close workbook. Sheet pages must be used for sheet events such
as worksheet change.

"Malik" wrote:

thx jo..dat was cool...it serves my purpose..but cud u make me understand the
way it has worked..i mean what is module..shall i save this as a macro? and a
module, unlike macros are run when a formula is inserted?
i mean if u cud throw sum light that shall really be helpful..thx so much
again.....

"Joel" wrote:

This is a custom function, but it behaves the same as other workbook function
such as =Sum(A1:B5). If you right click the tabb on the bottom of the
worksheet normally (sheet1) and select view code. then go to VBA Menu -
Insert Module. Simply copy the macro from this posting (starts with
"function" and ends witth "end function").the in you worksheet enter
=findtext(B1,A1).

"Malik" wrote:

is this a macro u wrote? i m sorry i though there might be a simpler formula
that can be used. anyways thx as i dont pretty well understand macros.


"Joel" wrote:

You need a custom function. Try the one below. I wrote a very simple method
of perfoming you request, but it has some limitations. If in the 2nd string
you have 13 it will match any number with 13 in it such as 135, 313,1013. if
this is a problem I can modify the function. this is just a simple solution.

Call with
=findtext(B1,A1)

Function findtext(Parm1 As String, Parm2 As String) As String
Dim NewNum As String
Dim ParseString As String

findtext = ""
ParseString = Parm1
Do While Len(ParseString) 0
CommaPos = InStr(ParseString, ",")
If CommaPos 0 Then
NewNum = Trim(Left(ParseString, CommaPos - 1))
ParseString = Trim(Mid(ParseString, CommaPos + 1))
Else
NewNum = Trim(ParseString)
ParseString = ""
End If

If InStr(Parm2, NewNum) = 0 Then
If Len(findtext) = 0 Then
findtext = NewNum
Else
findtext = findtext & ", " & NewNum
End If
End If
Loop


End Function

"Malik" wrote:

I have a set of digits in one cell separated by a comma and a bigger set or
master set of digits in the other cell. Is there a formula where I can
segregate the unmatching digits of the two cells onto the third cell

E.g,.

A1: 121, 211, 244
B1: 121, 211, 1314, 566, 667
What I want as result:
C1: 1314, 566, 667 (ideally B1 - A1)

Thanks