minus sign on the wrong side

  • Thread starter Thread starter Debra
  • Start date Start date
D

Debra

I work with many documents downloaded from an AS400
program. I just was upgraded to Office 2000 (9.0.3821 SR-
1) and now have problems with the numbers downloaded.
The AS400 program shows a figure as 118394.12-. Is there
a way, in Excel to change it to show -118394.12? Right
now I have to manually change every one.

Thanks for any help anyone can give me!
 
If all your numbers are 6 digets and two-place decimal, then try.....

=("-"&LEFT(A1,9))*1

Vaya con Dios,
Chuck, CABGx3
 
Debra

Manually, if numbers in one column, copy this formula to a cell in an adjacent
column and drag/copy down as far as you need.

When complete, copy this column then(in place) paste special>Values then
delete the original column.

Or use a macro to do all at once.

Sub Negsignleft()
Dim cell As Range
Dim rng As Range
''move minus sign from right to left on entire worksheet
On Error Resume Next
Set rng = ActiveSheet.Cells. _
SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
For Each cell In rng
If IsNumeric(cell.Value) Then
cell.Value = CDbl(cell.Value)
End If
Next cell
End Sub

Gord Dibben Excel MVP - XL97 SR2 & XL2002
 
I work with many documents downloaded from an AS400
program. I just was upgraded to Office 2000 (9.0.3821 SR-
1) and now have problems with the numbers downloaded.
The AS400 program shows a figure as 118394.12-. Is there
a way, in Excel to change it to show -118394.12? Right
now I have to manually change every one.

Thanks for any help anyone can give me!
Click to expand...

If you have XL2002 (I'm not sure when this appeared), and your range is a
column, you can select the column, then use the Data/Text to Columns wizard.
Under Step3 advanced options you can select to have trailing minus signs
treated as negative numbers.


--ron
 
I kind of liked that check. VBA's IsNumeric is a lot less forgiving than
Excel's IsNumber function.

From the immediate window:
?isnumeric("asdf")
False
?isnumeric("1234-")
True
?application.isnumber("1234-")
False

And it stops the routine from blowing up when you encounter a real text string.

Dana DeLouis has posted a similar version, but he leaves "on error resume next"
on. So that real text cells don't cause the sub to blow up real good.
 
VBA's Isnumeric is a LOT MORE FORGIVING!

If it can convert it to a number, it's a number to isnumeric.
 
Thank you so very much! It worked wonderfully.
-----Original Message-----
Debra

Manually, if numbers in one column, copy this formula to a cell in an adjacent
column and drag/copy down as far as you need.

When complete, copy this column then(in place) paste special>Values then
delete the original column.

Or use a macro to do all at once.

Sub Negsignleft()
Dim cell As Range
Dim rng As Range
''move minus sign from right to left on entire worksheet
On Error Resume Next
Set rng = ActiveSheet.Cells. _
SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
For Each cell In rng
If IsNumeric(cell.Value) Then
cell.Value = CDbl(cell.Value)
End If
Next cell
End Sub

Gord Dibben Excel MVP - XL97 SR2 & XL2002
Click to expand...
 
Those options were added to 2002 not available in 2000,
and I just double checked using Text to Columns which
bring up same stuff as the data import wizard. .

Should have thought about the difference between Excel and VBA
but now I know better and will remember that for the rest of day,
for sure..
 
Back
Top