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.

Dataset-Negative Sign at End of Number in Excel

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.


Download Excel Workbook


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.

Formula-Negative Sign at End of Number in Excel

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

Fill Handle

🔺 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))

Alternative formula

Read More: Excel Formula If Cell Contains Negative Number (6 Examples)


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.

Text to Columns-Negative Sign at End of Number in Excel

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.

Delimited

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

Tab

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

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.

Advanced Text Import Settings-Negative Sign at End of Number in Excel

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

Step 3 of 3

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

Outcome

Read More: How to Put a Negative Number in Excel Formula (4 Easy Methods)


Similar Readings


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.

Module Insertion-Negative Sign at End of Number in Excel

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

Macro

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.

Command Box-Negative Sign at End of Number in Excel

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

Outcome

Read More: Excel Formula to Return Zero If Negative Value is Found


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.

Have a quick visit to our amazing website and check out our recent articles on Excel. Happy Excelling.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo