4 Replies Latest reply on Jan 16, 2012 1:58 PM by Todd Rein

    Ignore text in formula

    Vince Ray

      I have a row which has both numbers and text in it, some both in the same cell.  eg. (text; 7) in one cell

      I then have another row underneath which contains numbers in each cell.

      I have a 'Total' column on the end of the sheet.

      I am trying to formulate each row to equal: Sum(A1*A$2)+(B1*B$2)+(C1*C$2)....etc, but want it to ignore the text, but include the number in the formula.

      Is there any way to do this?

        • 1. Re: Ignore text in formula
          Todd Rein Level 3

          You want to get the number from something like "text; 7"?  You can use text manipulation functions to do this.  If the cell that has the text/number you want is in cell C1, you could use a formula like this: = RIGHT(C1, LEN(C1) - FIND(";", C1)).

          • 2. Re: Ignore text in formula
            Vince Ray Level 1

            That works - thanks!
            Now, if I have this formula repeating for alot of cells, is there a quicker way to do it rather than going: =SUM(RIGHT(C1, LEN(C1) - FIND(";", C1))*C$2)+(RIGHT(D1, LEN(D1) - FIND(";", D1))*D$2), etc....to about Column "CH" ?

            • 3. Re: Ignore text in formula
              Vince Ray Level 1

              Actually - no I cant get the formula to work!

              It works fine in Excel, but not in Formcentral.  I just keep getting #VALUE

              I have even set up an excel sheet with a few cells exactly the same location as the ones in FormCentral, then I have copied the formula across from excel (where it works) - and into FormCentral, but it still doesnt work....


              • 4. Re: Ignore text in formula
                Todd Rein Level 3

                It's a bit hard to tell what might be the issue w/o seeing the specific table.  I tried your formulas, and saw the #VALUE...until I entered data in the cells.  I could probably help better if you could share your form to me (trein@adobe.com), and I can see things in context.