HELP - weird Negative Numbers
 

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

HELP - weird Negative Numbers
 

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

Michael Rhein wrote:
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

HELP - weird Negative Numbers
 

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

"Michael Rhein" wrote in message
om...
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

HELP - weird Negative Numbers
 

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" wrote in message
news:V14Ub.21640$fZ6.13414@lakeread06...
=SUBSTITUTE(A1,"-","")*-1
does shorter = simpler?
HTH

"Michael Rhein" wrote in message
om...
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

HELP - weird Negative Numbers
 

Good point Bob,
Heads Up - Michael!!

JMay

"Bob Phillips" wrote in message
...
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" wrote in message
news:V14Ub.21640$fZ6.13414@lakeread06...
=SUBSTITUTE(A1,"-","")*-1
does shorter = simpler?
HTH

"Michael Rhein" wrote in message
om...
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

HELP - weird Negative Numbers
 

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!