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

# Ignore text in formula

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

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

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

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....

tips?

• ###### 4. Re: Ignore text in formula

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.