If cell contains "+" add 0.33

[Fazila]

anyone able to help me with the above. I have a column which contains numbers with + or - i.e. 4+ or -4. I want to convert these numbers i.e. 4+ would equal 4.33 and -4 would equal 3.67 - any ideas on how I can achieve this? If the cell contains a whole number i.e. 4 then the formula is to return 4.

I've tried if(isnumber(search("+",a2)),sum(a2+0.33),a2) but get a #value error.

Thanks

Fazila

 

[AlanY]

try

=IF(A2<0,A2+0.33,IFERROR(INT(A2),SUBSTITUTE(A2,"+","")+0.33))

 

[Finalfight40]

Not 100% sure on the conditions but maybe this:

=IF(AND(ISNUMBER(A2), A2>=0), A2, IF(OR(ISNUMBER(SEARCH("+",A2)), ISNUMBER(SEARCH("-", A2))),SUBSTITUTE(A2, "+", "")+0.33, ""))

 

[Norie]

Perhaps.

=IF(ISNUMBER(SEARCH("+",A2)),SUBSTITUTE(A2,"+","")+0.33,IF(ISNUMBER(SEARCH("-",A2)),SUBSTITUTE(A2,"-","")-0.33,A2))

 

[Fazila]

try

=IF(A2<0,A2+0.33,IFERROR(INT(A2),SUBSTITUTE(A2,"+","")+0.33))

Thanks Alan works great to add 0.33 but doesn't work re when the cell contains -4

 

[Fazila]

Worked great thanks Finalflight40

 

[Fazila]

This worked too thanks Norie!

 

[AlanY]

it works for me

Book1
	A	B
2	4+	4.33
3	-4	-3.67
4	4	4
Sheet4

Cell Formulas
Range	Formula
B2	=IF(A2<0,A2+0.33,IFERROR(INT(A2),SUBSTITUTE(A2,"+","")+0.33))

 

[gaz_chops]

Or

=IF(ISTEXT(A1),SUBSTITUTE(A1,"+","")+0.33,IF(A1<0,A1+0.33,A1))