Find difference between cells data are separated by coma and space

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,244
Office Version
  1. 2010
Hi everybody,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Table A: I have data separated by coma & space in cells A1:A4<o:p></o:p>
<o:p></o:p>
A: Data Table <o:p></o:p>
<TABLE style="WIDTH: 161pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm" border=0 cellSpacing=0 cellPadding=0 width=215><TBODY><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 32pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=43 noWrap>
*<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 129pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=172 noWrap>
A<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
1<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>10, 20, 30, 40, 50, 60<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
2<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>11, 21, 31, 41, 51, 70<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
3<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>16, 19, 37, 48, 59, 81<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
4<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>18, 23, 33, 42, 57, 78<o:p></o:p>
</TD></TR></TBODY></TABLE>
<o:p></o:p>
<o:p></o:p>
Table B: I want to calculate difference between cells A1:A2, A2:A3 and so on…<o:p></o:p>
And if it is possible as shown in cell B2:B4<o:p></o:p>
<o:p></o:p>
B: Result Table <o:p></o:p>
<TABLE style="WIDTH: 346pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm" border=0 cellSpacing=0 cellPadding=0 width=461><TBODY><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 32pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=43 noWrap>
*<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 129pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=172 noWrap>
A<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 185pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=247 noWrap>
B<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
1<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>10, 20, 30, 40, 50, 60<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap> <o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
2<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>11, 21, 31, 41, 51, 70<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap> +1, +1, +1, +1, +1, +1<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
3<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>16, 19, 37, 48, 59, 81<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap> +5, -2, +6, +7, +9, +11<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
4<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>18, 23, 33, 42, 57, 78<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap> +2, +4, -4, -6, -2, -3<o:p></o:p>
</TD></TR></TBODY></TABLE>
<o:p></o:p>
Separating cells A1:A4 data in separate cells can do this. <o:p></o:p>
<o:p></o:p>
But if it is possible solution can be done as shown by VBA will be greater.<o:p></o:p>
So please can you help me for my request.<o:p></o:p>
<o:p></o:p>
Thanks and Regards,
Moti<o:p></o:p>
<o:p></o:p>
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Moli: Take column A and use the Text to Columns function so that each number will be in its own cell. Then writing the formula is much easier.
 
Upvote 0
Hi Moti,
Try this UDF:
Rich (BB code): 
<font face=Courier New>
Function DiffNums(OldNums, NewNums) As String
  Dim a, b, c#, d$, s$, i&
  a = Split(OldNums, ",")
  b = Split(NewNums, ",")
  If UBound(a) <> UBound(b) Then DiffNums = "#Amount?": Exit Function
  For i = 0 To UBound(b)
    c = Val(b(i)) - Val(a(i))
    If i > 0 Then d = ", "
    s = s & d & IIf(c > 0, "+", "") & c
  Next
  DiffNums = s
End Function
</FONT>
Formula of B2 cell: =DiffNums(A1,A2)

Regards,
Vladimir
 
Last edited:
Upvote 0
Hi ZVI,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Thank you very much for giving me an excellent UDF solution,<o:p></o:p>
It is working perfectly.<o:p></o:p>
<o:p></o:p>
As I calculated manually B2 Result was Wrong. That I noticed after applying <o:p></o:p>
Your huge solution.<o:p></o:p>
<o:p></o:p>
Here is your Great work Result 100% O.K <o:p></o:p>
<TABLE style="WIDTH: 249pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm" border=0 cellSpacing=0 cellPadding=0 width=332><TBODY><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 41pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=55 noWrap>
*<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 99pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=132 noWrap>
A<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 109pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=145 noWrap>
B<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
1<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>10, 20, 30, 40, 50, 60<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap> <o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
2<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>11, 21, 31, 41, 51, 70<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>+1, +1, +1, +1, +1, +10<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
3<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>16, 19, 37, 48, 59, 81<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>+5, -2, +6, +7, +8, +11<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
4<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>18, 23, 33, 42, 57, 78<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>+2, +4, -4, -6, -2, -3<o:p></o:p>
</TD></TR></TBODY></TABLE>
<o:p></o:p>
Thank you once again.<o:p></o:p>
<o:p></o:p>
Hi nbrcrunch, also thank you for your suggestion<o:p></o:p>
<o:p></o:p>
Thanks and Regards,
Moti<o:p></o:p>
 
Upvote 0
ZVI, nice solution. Though I've been "dabbling" with VBA on/off for years, I just never really take the time to do it.

For anyone interested, I morphed the code so that it will sum up the comma-delimited values in a single cell. This is the equivalent of using text-to-columns and then using a SUM function across the row. I could see something like this having greater general interest/appeal than the OP's request.

If your delimeter is something other than a comma, no problem, just replace the comma in the code with whatever your delimeter is.

Code: 
Function AddParts(OneCellNbrs) As Single
  Dim a, c, i&
  a = Split(OneCellNbrs, ",")
  
  For i = 0 To UBound(a)
    c = Val(a(i)) + c
  Next
  AddParts = c
End Function

To use: If A1 contains 1, 2, 3, 4, 5
Then in another cells type: =AddParts(A1)
 
Last edited:
Upvote 0
Hi nbrcrunch,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Fantastic It is some thing I did not think can be done! <o:p></o:p>
<o:p></o:p>
Here is your work Result with your sum function:<o:p></o:p>
<TABLE style="WIDTH: 309pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm" border=0 cellSpacing=0 cellPadding=0 width=412><TBODY><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 41pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=55 noWrap>
*<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 99pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=132 noWrap>
Numbers<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 109pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=145 noWrap>
Difference<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 60pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=80 noWrap>
Sum Col A<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
1<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>10, 20, 30, 40, 50, 60<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap> <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
210<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
2<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>11, 21, 31, 41, 51, 70<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>+1, +1, +1, +1, +1, +10<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
225<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
3<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>16, 19, 37, 48, 59, 81<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>+5, -2, +6, +7, +8, +11<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
260<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
4<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>18, 23, 33, 42, 57, 78<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>+2, +4, -4, -6, -2, -3<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
251<o:p></o:p>​
</TD></TR></TBODY></TABLE>
<o:p></o:p>
Thank you for giving me new ideas.<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Thanks and Regards,
Moti<o:p></o:p>
 
Upvote 0
Hi

Another option to add comma separated values:

Code: 
Function AddCSV(sCSV As String)
    AddCSV = Evaluate(Replace(sCSV, ",", "+"))
End Function
 
Upvote 0
pgc01:

Smartypants! ;)

Well, I'm just gunna blow it off to experience. Your 5,000+ posts compared to my measly little 1,700+

(By the way, I added your code to same file i had mine in. Now I have two ways to conquer the world.) (sinister laugh goes here!)
 
Upvote 0
Hi nbrcrunch

I though it was asking for the Evaluate() function. Notice that you can use the same idea for Vladimir's function:

Code: 
Function DiffCSVs(sCSV1 As String, sCSV2 As String) As String
    DiffCSVs = Join(Application.Text(Evaluate("{" & sCSV1 & "}-{" & sCSV2 & "}"), "+0;-0"), ",")
End Function
 
Upvote 0
Cool code, PGC!

It could be extended for math operation "+", "-", "*", "/", "^":
Rich (BB code): 
<font face=Courier New>
Function MathCSVs(sCSV1 As String, Operation As String, sCSV2 As String) As String
    MathCSVs = Join(Application.Text(Evaluate("{" & sCSV1 & "}" & Operation & "{" & sCSV2 & "}"), "+0;-0"), ", ")
End Function</FONT>
Application:
=MathCSVs(A2,"+",A1)
=MathCSVs(A2,"-",A1)
=MathCSVs(A2,"*",A1)
=MathCSVs(A2,"/",A1)
=MathCSVs(A2,"^",A1)

Vladimir
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
444
Attrazion
A
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
148
VBAEXCELNew
V
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
448
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
257
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
686
CrashOD
C

Forum statistics

Threads
1,202,983
Messages
6,052,907
Members
444,611
Latest member
ggwpnore

Share this page

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?

Disable AdBlock

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
Back
Top