Sum two or three different values in a cell differentiated with an alphabet and a comma in a cell

[anne7808]

Hi,
I wanted the sum total of different nos. separated by a comma in a row of cells in the exact order with commas in another cell
and also the alphabets written along with the nos. just appear as the normal letter alongside the total number.

like = _V, _S, _CC

 

[DILIPandey]

Hi anne,

Do you need below as output :-

17vv,18ss, 16cc



Regards,
DILIPandey

 

[anne7808]

I want this as the total. I need a formula for the *Total* cell that would produce this. Only the numbers are added and the alphabet is only for representation

 

[anne7808]

No sir, I want the letter to be there only once. only the numbers to get added. I have attached an image too. is this possible using any formula or macro?

Hi anne,

Do you need below as output :-

17vv,18ss, 16cc



Regards,
DILIPandey

 

[DILIPandey]

Okay.. would it be fine if I give you a formula which will extract the numbers from this text string into a different cell and from there you can get the sum ?
Or you need some vba (macro) solution for this ?


Regards,
DILIPandey

 

[anne7808]

Actually these data would be varying, so as long as I don't have to edit the data in different cells that could work.

I basically just want the total cell to show all the different numbers with their letter in the same order.
So if there is a VBA solution that would be good too.
Whichever is the simplest and easy to handle later.

And I am an excel beginner so I don't know complex things. If you could explain me whichever formula is suiting here. Thank you so much!

Okay.. would it be fine if I give you a formula which will extract the numbers from this text string into a different cell and from there you can get the sum ?
Or you need some vba (macro) solution for this ?


Regards,
DILIPandey

 

[DILIPandey]

Actually these data would be varying, so as long as I don't have to edit the data in different cells that could work.

You will have these data in two columns only.. or more columns will be there ?
Can you copy paste a sample here directly from your excel sheet ?


Regards,
DILIPandey

 

[anne7808]

http://server.myspace-shack.com/d21/flavour full.png


here is the full sheet which was visible in one screenshot.

the data is in one column only.

there is no scope of making a different column in the middle of this table as it would be printed. it would be safe to make a helper column after the 100th column as the table would not reach till there. or create a hidden column in middle?

You will have these data in two columns only.. or more columns will be there ?
Can you copy paste a sample here directly from your excel sheet ?


Regards,
DILIPandey

 

[anne7808]

sorry pasted the wrong link above

http://server.myspace-shack.com/d21/flavour full.png


here is the full sheet which was visible in one screenshot.

the data is in one column only.

there is no scope of making a different column in the middle of this table as it would be printed. it would be safe to make a helper column after the 100th column as the table would not reach till there. or create a hidden column in middle?

 

[Rick Rothstein]

Assuming that if any of your numbers are floating point numbers, then the decimal point is a dot and the decimal portion of the number never ends with trailing zeroes (that is, you never have a value like 12.30V, rather, it is always presented as 12.3V), then this UDF (user defined function) should work for you...

[table="width: 500"]
[tr]
	[td]Function LetterSum(Rng As Range) As String
  Dim X As Long, Num As String, Letter As String, V As Variant, Parts() As String
  Parts = Split(Replace(Join(Application.Index(Rng.Value, 1, 0), ","), " ", ""), ",")
  With CreateObject("Scripting.Dictionary")
    For X = 0 To UBound(Parts)
      If Len(Parts(X)) Then
        Num = Val(Parts(X))
        Letter = Mid(Parts(X), Len(Num) + 1)
        .Item(Letter) = .Item(Letter) + Val(Num)
      End If
    Next
    For Each V In .keys
      LetterSum = LetterSum & ", " & .Item(V) & " " & V
    Next
  End With
  LetterSum = Mid(LetterSum, 3)
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use LetterSum just like it was a built-in Excel function. For example,

=LetterSum(B2:AF2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.