Hi,
I have a single cell that contains the following character string: 1-3-5-7-9-11
Is it possible to create a formula to multiply the numeric values together? (1x3x5x7x9x11 = 10,395)
Thanks!!
Hi,
I have a single cell that contains the following character string: 1-3-5-7-9-11
Is it possible to create a formula to multiply the numeric values together? (1x3x5x7x9x11 = 10,395)
Thanks!!
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
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
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?
Last edited by dsannito; 10-21-2015 at 02:19 PM.
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.
Thank you so much! This worked perfectly!!!!!
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:
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.Formula:Please Login or Register to view this content.
Row\Col A B C 11-3-115-7-9-113 2456055In B1 {=PRODUCT(IFERROR(--TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",20)),(COLUMN(A:Z)-1)*20+1,20)),1))}
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks