VBA: Multiplying Values Separted by Comma

[ameya]

Hi Everyone,

I've run into a little roadblock, and any help would be greatly appreciated!

Right now, I have a few hundred cells, and within each cell, there are different number of values separated by commas. Each cell to the right of the cell has different values but the same exact number of values.

Ex: Cell A1 = 4,7,2,6.4 Cell B1 = 5,12,2,5
Cell A2 = 5,2 Cell B2 = 7,1


I want to multiply the values separated by commas together in column C. For instance, Cell C1 would equal 4*5+7*12+2*2+6.4*5.
I cannot use text to columns and then convert into numerical values because other data will be overwritten. I prefer using excel formulas, but if I have to use VBA that's fine too.
Thanks!

 

[VBA Geek]

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--TRIM(<font color="Red">MID(<font color="Green">SUBSTITUTE(<font color="Purple">A1,",",REPT(<font color="Teal">" ",99</font>)</font>),ROW(<font color="Purple">INDIRECT(<font color="Teal">"1:" & LEN(<font color="#FF00FF">A1</font>)-LEN(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">A1,",",""</font>)</font>)+1</font>)</font>)*99-98,99</font>)</font>),--TRIM(<font color="Red">MID(<font color="Green">SUBSTITUTE(<font color="Purple">B1,",",REPT(<font color="Teal">" ",99</font>)</font>),ROW(<font color="Purple">INDIRECT(<font color="Teal">"1:" & LEN(<font color="#FF00FF">B1</font>)-LEN(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">B1,",",""</font>)</font>)+1</font>)</font>)*99-98,99</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2013
	A	B	C
1	4,7,2,6.4	5,12,2,5	140
Sheet1

 

[ameya]

Thank you so much! You are seriously awesome!

One last question: If I wanted to add up the cells in B and Put that in D1, how would I do that?
D1 = 5 +12 + 2 +5

Thanks!

 

[Br0ck]

Put this in D1...

=(MID(B1,1,1)+(MID(B1,3,2)+(MID(B1,6,1)+(MID(B1,8,1)))))

 

[VBA Geek]

Np.
Just remove the first argument of the SUMPRODUCT in the previous formula, so just keep the second argument

Thank you so much! You are seriously awesome!

One last question: If I wanted to add up the cells in B and Put that in D1, how would I do that?
D1 = 5 +12 + 2 +5

Thanks!

 

[ameya]

Thanks guys! It works now!
Appreciate the help.