|
|
|
|
|
|
Excel
|
Convert numbers with trailing minus(es) to real numbers
|
|
Ease of Use
|
Easy
|
Version tested with
|
2003
|
Submitted by:
|
Ken Puls
|
Description:
|
This code corrects numbers with trailing minus signs (minus on the right) to real negative numbers (minus on the left or brackets depending on the number format).
|
Discussion:
|
The problem with trailing minus signs is that Excel sees them as text, so they can't be used in formulas. This code converts these text strings to real negative numbers. This code also limits the selected range to ignore cells outside of the used range of the spreadsheet. While this is not strictly necessary, it does eliminate the problem of the code looping through every cell on the spreadsheet if someone selects the entire sheet and runs the code.
|
Code:
|
instructions for use
|
Option Explicit
Sub FixTrailingMinus()
Dim cl As Range, theRange As Range
If Selection.Cells.Count = 1 Then
Selection.Value = CDbl(Selection)
Else
Set theRange = Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
For Each cl In theRange
If IsNumeric(cl) Then
If Not cl.Value = vbNullString Then
cl = CDbl(cl)
End If
End If
Next cl
End If
End Sub
|
How to use:
|
- Copy above code.
- In Excel press Alt + F11 to enter the VBE.
- Press Ctrl + R to show the Project Explorer.
- Right-click desired file on left (in bold).
- Choose Insert -> Module.
- Paste code into the right pane.
- Press Alt + Q to close the VBE.
- Save workbook before any other changes
|
Test the code:
|
- Fill a range with numbers that have the minus sign AFTER the number
- Create a formula that adds a number to one that you have entered (to prove it won't work)
- Select the entire range
- From the main Excel interface, press Alt + F8 to open the macro dialog box
- Choose FixTrailingMinus and click Run
- Verify that the numbers have been changed to real numbers (including your test formula.)
|
Sample File:
|
ConvertTrailingMinus.zip 7.64KB
|
Approved by mdmackillop
|
This entry has been viewed 96 times.
|
|