How do I extract numbers from a single cell and multiply them together?

Maybe something like this

Enter in B1:

=LEFT(A1,FIND("-",A1)-1)

then in C1:

=IFERROR(SUM(TRIM(MID(SUBSTITUTE("-"&$A1,"-",REPT(" ",50)),50*COLUMNS($A:A),50)))*B1,"")

and pull formula to the right. The last value will be the result.


Data Range

	A	B	C	D	E	F	G	H
1	1-3-5-7-9-11	1	1	3	15	105	945	10395

Thank you!! That was way more complex than I expected.

Is there a way to keep the function confined to one cell?

I'm able, for example, to multiply the value of three numbers within a single cell (i.e. 1-7-23) using the formula below. The trouble for me is adding additional numbers beyond 3.

A1 Cell content: '1-7-23
=PRODUCT(LEFT(A1,1)*MID(A1,3,1+IF(MID(A1,4,1)="-",0,1))*RIGHT(A1,2))

Answer: 161

Thoughts?

There is another way

assuming your text values in A1 on Sheet1

Go to Formulas, Name Manager and enter this formula

=EVALUATE(SUBSTITUTE(Sheet1!A1,"-","*"))

Name it MText

Close Name Manager and enter formula in B1

=MText

Save your workbook as Macro-Enabled.

You're welcome. Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

As an afterthought this array formula seems to work:

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Row\Col	A	B	C
1	1-3-115-7-9-113	2456055	In B1 {=PRODUCT(IFERROR(--TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",20)),(COLUMN(A:Z)-1)*20+1,20)),1))}