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

We have a column of random numbers as shown in the image below. We want to fix the negative signs’ positions in the entries.

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

### Method 1 – Applying Formulas

Steps:

• Apply the below formula in the C4 cell.
`=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.

• Drag the Fill Handle to fill all cells.

You can use an alternative formula to move negative signs:

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

### Method 2 – Using the Text to Columns Feature

Steps:

• Select the entire range or column.
• Go to Data and the Data Tools section.
• Click Text to Columns.

• This opens the Convert Text to Column Wizard -Step 1 of 3 window.
• Mark Delimited for Choose the file type that best describes your data.
• Click Next.

You’ll get the Convert Text to Column Wizard -Step 2 of 3 window.

• Tick Tab for Delimiters, then select Next.

Another Convert Text to Column Wizard -Step 3 of 3 window appears.

The Advanced Text Import Settings dialog box appears.

• Tick the Trailing minus for negative numbers option.
• Click OK.

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

Steps:

• Press Alt + F11 or move to the Developer tab and select Visual Basic to open the Microsoft Visual Basic window.
• Click Insert and select Module.

• 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``````

In the sections:

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

2 – declares the variable as Range.

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

4 – executes 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. Inserting a minus using an Ampersand (&) deals with the negative signs.

• Hit F5 to run the macro.
• Excel brings out the Provide Range command box.
• In the box, assign the desired range, then click on OK.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF