How to fix/convert trailing negative signs to real numbers in Excel?
When working with imported or legacy data in Excel, it is common to encounter numbers that display a negative sign at the end, such as 90- instead of -90. These trailing negative signs usually arise from accounting or data export conventions, and if left uncorrected, they prevent proper numerical calculations, sorting, or analysis in Excel. To ensure data accuracy and usability, it is often necessary to convert these numbers so the negative sign appears in front of the value, making it a true negative number recognized by Excel.
The following guide introduces several effective solutions for fixing or converting trailing negative signs to real numeric values in Excel. Each method caters to different scenarios and user skill levels, helping you choose the most suitable approach for your data cleaning tasks.
Fix trailing negative signs in cells with formulas
Fix trailing negative signs in cells with Text to Columns function
Fix trailing negative signs in cells with VBA code
Quickly fix trailing negative signs in cells with Kutools for Excel
Fix trailing negative signs in cells with formulas
Suppose you have a range of cells, for example A1:A11, which contain numbers formatted with trailing negative signs instead of leading ones. Since Excel recognizes only numbers with the negative sign at the front for calculation, using a formula provides a flexible and straightforward way to reformat these values. This method is especially useful if you prefer to avoid altering the original data and need a dynamic or easily updateable solution.
To convert the numbers, use an adjacent blank cell for your formula. For example, click cell B1 next to your data and enter one of the following formulas. After entering the formula, press Enter, and the value in the cell will be converted to a correctly formatted negative number if it had a trailing negative sign. Next, drag the fill handle down to apply the formula to other cells in the column to fix all values as needed. This approach keeps your original data intact and displays the corrected results in a separate column.
=IF(RIGHT(A1,1)="-",SUBSTITUTE(A1,"-","")*-1,A1)=VALUE(IF(RIGHT(A1,1)="-",RIGHT(A1,1)&LEFT(A1,LEN(A1)-1),A1))
Formula Explanation and Additional Tips:
- The formulas provided typically check if the last character of the value is a minus sign ("-"). If so, they move the negative sign to the front; otherwise, they return the number as is.
- If your numbers might include spaces or special symbols, consider using functions like TRIM or SUBSTITUTE to handle irregularities.
- After applying the formulas, if you need to replace the original data, copy the results and use Paste Special > Values to overwrite the initial column with corrected values.
- If you encounter errors due to text formatting, ensure the cell is formatted as General or Number after the formula is applied.
Advantages: Does not alter original data; easy to audit or revert.
Limitations: May require additional steps if you want to replace the original values; some non-standard data formats could require extra adjustments.
Fix trailing negative signs in cells with Text to Columns function
The Text to Columns feature in Excel offers an alternative way to fix numbers with trailing negative signs. This built-in function is especially useful when working with a large volume of data, and it is designed to recognize accounting-specific negative formatting, converting values such as 90- into -90 automatically. This solution is particularly effective when all affected numbers are within a single column and are consistently formatted.
Follow these steps to use the Text to Columns feature to correct trailing negative signs:
1. Select the column or range of cells containing the numbers with trailing negative signs.
2. Go to the Ribbon and click Data > Text to Columns. This opens the Convert Text to Columns Wizard. See screenshot:
3. In the wizard, simply click the Next button through Step 1 and Step 2. No changes are needed for delimiters if you are not splitting data.
4. At Step 3, click the Advanced… button to open the Advanced Text Import Settings dialog box. Check the Trailing minus for negative numbers option, then click OK. See screenshot:
5. Click Finish to apply the changes. All numbers with a trailing negative sign will now be converted into standard negative integers that Excel recognizes.
Tips and Troubleshooting:
- This method works only for numeric values that are not mixed with other text.
- If any cell contains non-numeric characters, the feature may fail to recognize and convert the trailing negative sign.
- Always create a backup of your data before running Text to Columns, as the operation alters the existing values in place.
- If changes are not reflected instantly, double-check that the correct option was selected in the Advanced settings, and ensure your data actually contains the trailing negative format.
Advantages: Quick and does not require formulas or programming knowledge; processes large data ranges at once.
Limitations: Overwrites existing data; not suitable for mixed or irregular formats.
Fix trailing negative signs in cells with VBA code
For users who are comfortable with macros or wish to automate the process across one or more ranges, a simple VBA (Visual Basic for Applications) script can rapidly convert all numbers with trailing negative signs to standard negative numbers. This approach is especially suitable for repeated use, large datasets, or when you need to customize the conversion logic. Always save your work before running VBA scripts, as they make direct changes to your spreadsheet.
To use a VBA solution, perform the following steps:
1. Highlight the range containing numbers you want to correct, then click the Developer tab > Visual Basic. In the Microsoft Visual Basic for Applications window that appears, click Insert > Module, and paste the following VBA code into the module:
VBA: fix all trailing negative signs in a selected range.
Sub FixNegative()
'Updateby20251113
Dim rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeConstants, xlTextValues)
For Each rng In WorkRng
xValue = rng.Value
If VBA.Right(xValue, 1) = "-" Then
rng.Value = "-" & VBA.Left(xValue, VBA.Len(xValue) - 1)
End If
Next
End Sub
2. After entering the code, click the 
"Run" button (or press F5) to execute the macro. You will be prompted to select the range to which you wish to apply the fix. See screenshot:
3. Click OK. The macro will scan the selected range and convert all values with trailing negative signs into standard negative numbers. Your data will now be ready for numeric calculations and further analysis.
Practical Notes:
- If you're prompted with security warnings, enable macros to proceed.
- If the code does not run as expected, verify your range selection and that your data follows a consistent pattern (e.g., only one trailing hyphen per affected cell).
- For large datasets, consider running the code on sample data first to confirm accuracy.
- VBA solutions are best used on copies of data, as the changes cannot be undone with the Undo feature.
Advantages: Fully automated, highly customizable for advanced needs, and can be saved for repeated use.
Limitations: Requires access to the Developer tab and basic familiarity with VBA; changes are irreversible via Undo after execution.
Quickly fix trailing negative signs in cells with Kutools for Excel
If you frequently manage data imported from external systems, a dedicated tool can simplify and expedite the process. Kutools for Excel provides a convenient utility named "Change Sign of Values" that allows you to fix trailing negative signs with just a few clicks. This is particularly useful for non-technical users or when dealing with multiple columns or tangled data that would otherwise require complex formulas or scripting.
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now
If you have installed "Kutools for Excel", follow these steps to fix trailing negative signs:
Step1. Select the range containing the numbers with trailing negative signs that you wish to correct. Then, go to Kutools > Content > Change Sign of Values.
Step2. In the pop-up dialog, check the Fix trailing negative signs option, then click OK or Apply. All numbers with trailing negative signs in your range will be immediately converted to standard negative numbers.
The "Change Sign of Values" feature in Kutools for Excel can also help you batch convert all positive numbers to negative or vice versa, and provides several other sign-changing utilities for fast data transformation. For more information about this feature, you can visit the Change Sign of Values feature description.
Notes and Troubleshooting:
- Be sure to review the selection before applying the changes, as the operation modifies your data in place.
- If you encounter unexpected results, check whether the data contains numbers mixed with special characters or text strings, which may need extra cleaning.
- Kutools features offer user-friendly interfaces and dialog prompts, reducing the risk of accidental errors.
Advantages: Fast, intuitive, and accessible to users of all levels; suitable for batch processing and complex datasets.
Limitations: Requires installation of Kutools for Excel; full functionality depends on a valid license after the trial period.
- Change negative numbers to positive
- Change positive numbers to negative
- Reverse signs of values in cells
- Change negative number to zero
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in