minus sign on the wrong side

[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!

 

[CLR]

If all your numbers are 6 digets and two-place decimal, then try.....

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

Vaya con Dios,
Chuck, CABGx3

 

[Gord Dibben]

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

 

[Gord Dibben]

And the formula is........

=IF(ISNUMBER(A1),A1,SUBSTITUTE(A1,"-","")*-1)

Gord

 

[Ron Rosenfeld]

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 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

 

[Dave Peterson]

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.

Good point David.

The IsNumeric line is superfluous with the data being text. Must have sneaked
its way in there from some test or...??

Gord

That's interesting, I know that it works,
but the cell is selected because SpecialCells identified the cell
as text, but the IsNumeric tests the same cell as numeric *before*
it is converted.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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!

 

[Dave Peterson]

VBA's Isnumeric is a LOT MORE FORGIVING!

If it can convert it to a number, it's a number to isnumeric.

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.

Good point David.

The IsNumeric line is superfluous with the data being text. Must have sneaked
its way in there from some test or...??

Gord

That's interesting, I know that it works,
but the cell is selected because SpecialCells identified the cell
as text, but the IsNumeric tests the same cell as numeric *before*
it is converted.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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!

 

[Debra]

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

 

[David McRitchie]

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..