If cell contains character, then use amount with a + or - sign

[Rock5150]

Hello all,

I have a spreadsheet where column E needs to be filled with an amount depending on whether another cell in column F has either a + or - sign.

For example:

F2 has "+ ACH PreAuthorized", G2 has "322.1". I would like to have cell E2 fill in the amount "+322.1". So in cell E2, if F2 has a plus sign, then put in the amount in E2. If it has a - sign, put in the amount from G2 into E2 but put a - sign in front of the amount. If there is no sign at all, put a zero or leave blank.

Thanks

 

[jai9]

Hi,

Try,

=IF(LEFT(F2,1)="+","+"&G2,IF(LEFT(F2,1)="-","-"&G2,""))

Jai

 

[Rick Rothstein]

Try putting this formula in E2...

=1*(LEFT(F2)&G2)

 

[Sanjeev1976]

Try putting this formula in E2...

=1*(LEFT(F2)&G2)

A small correction in the above formula :

=1*(LEFT(F2,1)&G2)

 

[Rick Rothstein]

A small correction in the above formula :

=1*(LEFT(F2,1)&G2)

You do not need to include the second argument for the LEFT function when that argument is 1 because the function defaults to 1 when its omitted (same holds true for the RIGHT function as well, but not for the MID function though). The formula I posted works fine as posted... no correction is necessary. Try it.