Symptoms
When you import a worksheet into Microsoft Excel and that worksheet contains numbers with minus signs on the right (for example, 12345-), Microsoft Excel may not treat these numbers as negative numbers.
Cause
Microsoft Excel treats these numbers as text because the minus sign appears to the right side of the number.
Workaround
There are two ways to solve this problem:
1. Use a worksheet formula. -or-
2. Create a macro.
Use the following formula in a worksheet:
=IF(RIGHT(A1,1)="-","-"&LEFT(A1,LEN(A1)-1),A1)*1
=IF(RIGHT(A1,1)="-",SUBSTITUTE(A1,"-","")*-1,A1)
=VALUE(IF(RIGHT(A1,1)="-",RIGHT(A1,1)&LEFT(A1,LEN(A1)-1),A1))
=IF(RIGHT(A1,1)="-",SUBSTITUTE(A1,"-","")*-1,A1)
=VALUE(IF(RIGHT(A1,1)="-",RIGHT(A1,1)&LEFT(A1,LEN(A1)-1),A1))
For example, follow these steps:
1. Enter the following in a worksheet:
A1: 3
A2: 2-
A3: 1-
A4: 4
A5: 6-
2. In cell B1, enter the above formula.
3. Fill the formula in B1 down to B5.
4. Select cells B1:B5 and click Copy on the Edit menu.
5. Select cell A1 and click Paste Special on the Edit menu.
6. Click Values and click OK.
7. Select B1:B5 and click Delete on the Edit menu.
*******************************************************************************
1. Select the numbers you want to fix the trailing minus sign, and click Data à Text to Columns.
2. In the Text to Columns wizard, directly click Next à Next to go to the Step 3 of 3 dialog, and click Advanced.
3. In the Advanced Text Import Settings dialog, just check Trailing minus for negative numbers option, and click OK to close it.
4. Then click Finish to close Text to Columns dialog, and the minus sign of the negative numbers have been moved from back to front.
*******************************************************************************
If you are familiar with VBA, here is one can help you.
1. Press Alt + F11 keys simultaneously to open Microsoft Visual Basic for Applications window.
2. Click Insert à Module, and paste below VBA code to the new Module window.
VBA: Move minus sign from back to front (right to left)
Sub FixTrailingNumbers()
'UpdatebyKutoolsforExcel20160409
Dim xRg As Range
Dim xCol As Range
Dim xTxt As String
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Select a range", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
For Each xCol In xRg.Columns
xCol.TextToColumns Destination:=xCol, TrailingMinusNumbers:=True
Next
End Sub
3. Click Run button or F5 key to execute this code, and a Kutools for Excel dialog pops out to remind you select a range to work.
4. Click OK, and now the minus signs of negative numbers have been fixed correctly.
------------------***************------------------
https://www.extendoffice.com/documents/excel.html
