HELP - weird Negative Numbers

[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

 

[Frank Kabel]

Hi Michael
one way: If you have Excel 2002+: Select the cells. goto 'Data - Text
to columns'. Click OK, OK, then Advanced. Check the treat trailing
minus as negative checkbox, then Finish. This should do

Also have a look at:
http://www.mcgimpsey.com/excel/postfixnegatives.html

HTH
Frank

 

[JMay]

=SUBSTITUTE(A1,"-","")*-1
does shorter = simpler?
HTH

 

[Bob Phillips]

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)

 

[JMay]

Good point Bob,
Heads Up - Michael!!

JMay

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)

 

[Michael Rhein]

Guys, tons of thanks !!....
the Subtitute formula works like Magic !..
Thanks again
MR



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!