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 'Test if selection is a single cell If Selection.Cells.Count = 1 Then 'If so, convert only the one cell Selection.Value = CDbl(Selection) Else 'If not, limit the selected range to only include cells with text strings in them Set theRange = Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues) 'Loop through each cell an test if it is a number For Each cl In theRange If IsNumeric(cl) Then 'If it is a number, test to ensure it isn't blank '(VBA's IsNumeric function treats blanks as numbers) If Not cl.Value = vbNullString Then 'If it is not blank, convert the text to a double '(This puts the negative on the correct side) cl = CDbl(cl) End If End If Next cl End If End Sub

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes
 

Test the code:

  1. Fill a range with numbers that have the minus sign AFTER the number
  2. Create a formula that adds a number to one that you have entered (to prove it won't work)
  3. Select the entire range
  4. From the main Excel interface, press Alt + F8 to open the macro dialog box
  5. Choose FixTrailingMinus and click Run
  6. 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.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express