Negative numbers entered as text

M

macropod

Hi,

To chnge the text to numbers:
.. Insert a '1' in any cell.
.. Copy the cell
.. Select the cells you want to change
.. Use Edit|Paste Special|Values > Multiply
 
H

Harlan Grove

macropod said:
To chnge the text to numbers:
. Insert a '1' in any cell.
. Copy the cell
. Select the cells you want to change
. Use Edit|Paste Special|Values > Multiply
Click to expand...
....

Word MVPs should know better than to answer Excel questions.

Note that the OP mentioned negative signs at the END of numbers, e.g.,

12,345-

which is (unfortunately) a common mainframe/minicomputer number
format. Put in a cell, follow your steps, and the cell contents will
remain unchanged, so remain text.
....

Ask Excel-specific questions in Excel-specific newsgroups.

If these values are all in the same column, then select the range of
values you want to convert in that column. Otherwise, if these values
are in multiple columns, you'll need to repeat this process on each
column individually.

Select the values to convert.

Run the menu command Data > Text to Columns.

Click the Next button twice to move to Step 3 of 3 in the Convert Text
to Columns Wizard. Since you selected only a single column, the first
two steps don't matter.

Click the Advanced... button, and ensure that the checkbox next to the
caption 'Trailing minus for negative numbers' is checked, then click
OK.

Back in the Step 3 of 3 dialog, click the Finish button.
 
M

macropod

Hi Harlan,

Prior to Excel 2003, there is no 'Trailing minus for negative numbers' option.

The following macro should work with all Excel versions from '97 onwards:

Sub FixNegatives()
Dim CellContent As Range
For Each CellContent In ActiveSheet.UsedRange
If CellContent.HasFormula = False Then _
If Right(CellContent, 1) = "-" And Left(CellContent, 1) <> "-" Then _
If IsNumeric(Mid(CellContent, 1, Len(CellContent) - 1)) Then _
CellContent.Formula = "-" & Mid(CellContent, 1, Len(CellContent) - 1)
Next
End Sub

I've included some error-checking to skip over cells with non-numeric content.
 
M

macropod

And here's a slightly more efficient and effective version:

Sub FixNegatives()
Dim CellContent As Range
For Each CellContent In ActiveSheet.UsedRange
If CellContent.HasFormula = False Then _
If IsNumeric(CellContent) Then _
If Right(CellContent, 1) = "-" Then _
CellContent.Formula = "-" & Left(CellContent, Len(CellContent) - 1)
Next
End Sub
 
H

Harlan Grove

macropod said:
And here's a slightly more efficient and effective version:

Sub FixNegatives()
Dim CellContent As Range
For Each CellContent In ActiveSheet.UsedRange
  If CellContent.HasFormula = False Then _
    If IsNumeric(CellContent) Then _
      If Right(CellContent, 1) = "-" Then _
        CellContent.Formula = "-" & Left(CellContent, Len(CellContent) - 1)
Next
End Sub
Click to expand...
....

Fair point in your other reply about the trailing minus feature being
introduced in Excel 2003.

However, if you're going to suggest a macro, you should restrict it to
operate on the selected range rather than the whole used range. And
it's not unlikely the OP would also want to convert positive numbers
in the same process as negative numbers. If so, the OP could try


Sub foo()
Dim c As Range, t As String
For Each c In Selection.SpecialCells(xlCellTypeConstants,
xlTextValues)
t = c.Value2
If IsNumeric(t) Then c.Formula = CDbl(t)
Next c
End Sub
 
Top