If then statement

[tedd]

I am trying to add numbers in a formula but one of the numbers contains a letter (i.e. 1231.44 + 456.77 D). The D is for debit in this case (accounting) and I think I can use an If then statement. Can anyone help?

[Trebor76]

Hi there,

Assuming the numbers start in cell A1, enter/copy the formula below into whatever cell you like and copy down as required. You haven't mentioned it, but the formula only deals with D (debit) entries so you'll have to tweak it if there are C's (credit) entries as well.

HTH

Robert

=IF(ISERROR(SEARCH("D",A1)),A1,ABS(LEFT(A1,SEARCH("D",A1)-2)))

[Bryan Hessey]

I am trying to add numbers in a formula but one of the numbers contains a letter (i.e. 1231.44 + 456.77 D). The D is for debit in this case (accounting) and I think I can use an If then statement. Can anyone help?

Hi,

together with Robert's answer, you can display a number followed by a D or C and still be able to use that number in calculations. Set the cell format to Custom with something like $#,##0.00" D";$#,##0.00" C" or $ ###,##0.00" DR";$ ###,##0.00" CR" etc

hth
---