How to Move Negative Sign at End to Left of a Number in Excel

Often, users enter a negative sign at the end of a number in Excel. According to Excel usage, a negative sign at the end of any number converts the number into text. As a result, Excel aligns them to the left in the cells. However, there are multiple ways to move the Negative Signs at the End to Left of a Numbers in Excel.

Let’s say we have a column of randomly entered numbers as shown in the image below. We want to fix the negative signs’ positions in the entries.

In the article, we demonstrate multiple Formulas, the Text to Columns feature, and VBA Macro to move the negative sign at the end to left of a number in Excel.

3 Easy Methods to Move Negative Sign at End to Left of a Number in Excel Error

Method 1: Applying Formulas to Move Negative Sign at End to Left of a Number in Excel

A custom formula with multiple functions such as IF, RIGHT, and SUBSTITUTE or VALUE, IF, RIGHT, and LEFT can fix the negative signs at the ends of numbers. The formula converts the entries into normal negative numbers.

Step 1: Add a helper column adjacent to the existing column. Apply the below formula in the C4 cell as depicted in the following picture.

=IF(RIGHT(B4,1)="-",SUBSTITUTE(B4,"-","")*-1,B4)

In the formula, the IF function runs a logical_test using the Right function to find a negative sign (1 character). If the test outcome is TRUE, then the SUBSTITUTE function removes the end negative sign and multiplies it with -1 otherwise the entry remains unchanged.

Step 2: Drag the Fill Handle to fix the negative signs at the ends.

🔺 You can use an alternative VALUE, IF, RIGHT, and LEFT functions made formula to move negative signs at end to left.

=VALUE(IF(RIGHT(B4,1)="-",RIGHT(B4,1)&LEFT(B4,LEN(B4)-1),B4))

Method 2: Using Text to Columns Feature to Place Negative Sign at End to Left of a Number

Excel offers the Text to Columns feature in its Data tab. Users can use the Text to Columns feature to relocate negative signs at the ends to left of a number in Excel.

Step 1: Highlight the entire range or column. After that, go to Data > Data Tools section > Click Text to Columns.

Step 2: Clicking Text to Columns fetches Convert Text to Column Wizard -Step 1 of 3 window. In the window, mark Delimited as Choose the file type that best describes your data. Click Next.

Step 3: Tapping on Next brings the Convert Text to Column Wizard -Step 2 of 3 window. Tick Tab as Delimiters then Next.

Step 4: Another Convert Text to Column Wizard -Step 3 of 3 window appears. Click on Advanced.

Step 5: The Advanced Text Import Settings dialog box appears. In the dialog box, tick the Trailing minus for negative numbers option. Afterward, click OK.

Step 6: Excel returns to the Convert Text to Column Wizard -Step 3 of 3 window. Click on Finish.

🔺 The trailing negative signs of the numbers get moved as shown in the below screenshot.

Method 3: Using VBA Macro to Relocate Negative Sign at End to Left of a Number

Excel VBA Macros are very powerful tools to achieve custom outcomes. The VBA LEFT function can fetch entries without trailing negative signs. Then a simple concatenation with a negative sign, results in the insertion of the sign in the front.

Step 1: Press ALT+F11 or move to Developer tab > Visual Basic to open Microsoft Visual Basic Window. In the window, click Insert > Module.

Step 2: Paste the following macro into the inserted Module.

Sub Fixing_NegativeSign()
Dim Rng As Range
Dim WrkRng As Range
On Error Resume Next
Set WrkRng = Application.Selection
Set WrkRng = Application.InputBox("Provide Range", "Exceldemy", WrkRng.Address, Type:=8)
Set WrkRng = WrkRng.SpecialCells(xlCellTypeConstants, xlTextValues)
For Each Rng In WrkRng
  xValue = Rng.Value
  If VBA.Right(xValue, 1) = "-" Then
  Rng.Value = "-" & VBA.Left(xValue, VBA.Len(xValue) - 1)
  End If
 Next
End Sub

➤ From the above image, in the sections,

1 – begin the macro code by declaring the VBA Macro Code’s Sub name.

2 – declare the variable as Range.

3 – assign the WrkRng variable to application selection, fetch an input box, and special cells.

4 – execute a VBA FOR loop to find the trailing minus signs using the VBA RIGHT function. Then the VBA LEFT function fetches the entries without negative signs. Finally, inserting a minus using an Ampersand (&) deals with the negative signs.

Step 3: Hit F5 to run the macro. Afterward, Excel brings out the Provide Range command box. In the box, assign the desired range, then click OK.

🔺 Now, return to the worksheet. You see, the trailing minuses get inserted in the front. Thus, the text formatted numbers become normal negative numbers.

Download Excel Workbook

Moving Negative Sign at End to Left of a Number.xlsm

Conclusion

This article describes ways such as applying formulas, the Text to Columns feature, and VBA Macro to move negative sign at the end to left of a number in Excel. Users can use any of the methods depending on their data types. Comment if you have further inquiries or have anything to add.

Get FREE Advanced Excel Exercises with Solutions!