Convert text "567-" to numeric -567

  • Thread starter Thread starter beth
  • Start date Start date
B

beth

Imported SAP report data uses trailing minus sign. On
import we need to convert this to a negative numeric
value. Field lengths will vary. Anyone have a clean way to
do the conversion?
sample data:
123
123-
1-
1234

All is left-justified text.
 
beth said:
Imported SAP report data uses trailing minus sign. On
import we need to convert this to a negative numeric
value. Field lengths will vary. Anyone have a clean way to
do the conversion?
sample data:
123
123-
1-
1234

All is left-justified text.
Click to expand...

One way:
=IF(RIGHT(A1,1)="-",-VALUE(LEFT(A1,LEN(A1)-1)),VALUE(A1))
 
Here's one way with a formula:

=IF(RIGHT(A1)="-",LEFT(A1,LEN(A1)-1)*-1,A1*1)

HTH
Jason
Atlanta, Ga
 
With excel 2002 and later you can change that simply by selecting the column
with numbers, do data>text to columns,
click next twice and the click finish. The default setting will convert the
trailing minuses. Excel 97 and 2000
you can either use a help column and copy down this formula


=IF(RIGHT(A1)="-",---LEFT(A1,LEN(A1)-1),A1)


then copy the help column and paste it as values in place, the delete the
original column

Finally you could do as me and use J.E. McGimpsey's macro

http://www.mcgimpsey.com/excel/postfixnegatives.html
 
Back
Top