HELP - weird Negative Numbers

  • Thread starter Thread starter Michael Rhein
  • Start date Start date
M

Michael Rhein

Newbie questions :
I am currently working with some output report from my company
database in *.txt format. After converting to *.xls, i got the
negative records shows negative sign AFTER the number (example : 254
-) instead of before the number like it normally does.

Let say the number is in cell B1. So I use :
=IF(RIGHT(B1,1)="-",LEFT(B1,(LENB1)-1)))*-1,B1) to make it right.

Is there any simpler way rather than having some conditions ?

TIA
michael
 
Hi JMay,

Probably yes, but you should check the form before negating it. This then
gives

=IF(ISERROR(FIND("-",A2)),A2,SUBSTITUTE(A2,"-","")*-1)

which ceases to be shorter <vbg>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Good point Bob,
Heads Up - Michael!!

JMay

Bob Phillips said:
Hi JMay,

Probably yes, but you should check the form before negating it. This then
gives

=IF(ISERROR(FIND("-",A2)),A2,SUBSTITUTE(A2,"-","")*-1)

which ceases to be shorter <vbg>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Guys, tons of thanks !!....
the Subtitute formula works like Magic !..
Thanks again
MR
 
Back
Top