Swap trailing minus to leading

[Curt]

I just imported a very large text file where the numbers were formatted with
a trailing sign so I now have sells which look like this 000478-. I'd like to
change that so thet they are -478. I've been doing it manually, but I figured
a macro to search the entire spreadsheet would be much more efficient. All of
the numbers are 7 characters with the 8th position being the sign. There are
also many numbers that were either 0 or positive which imported just fine, so
I need to leave them alone. Any suggestions as to how best solve this?

 

[Alan Moseley]

I would just put a formula in a cell on the same line as the number, for
example if your 000478- was in cell A1 then in cell B1 type:-

=IF(RIGHT(A1,1)="-",-INT(LEFT(A1,LEN(A1)-1)),INT(A1))

Now just copy this down to the bottom of your data. If necessary, copy the
column and paste the values over the top of the data in the original column
and delete your formula column.

 

[Ron Rosenfeld]

I just imported a very large text file where the numbers were formatted with
a trailing sign so I now have sells which look like this 000478-. I'd like to
change that so thet they are -478. I've been doing it manually, but I figured
a macro to search the entire spreadsheet would be much more efficient. All of
the numbers are 7 characters with the 8th position being the sign. There are
also many numbers that were either 0 or positive which imported just fine, so
I need to leave them alone. Any suggestions as to how best solve this?

If you have a reasonably modern version of Excel, there is an option for
handling trailing minus signs in the Data/Text-to-columns wizard. This option
is found at Step 3 after selecting the "Advanced" button.

Select your data
Data/TExt-to-Columns
Next
Next
Advanced
Select the trailing minus option
OK
Finish
--ron

 

[Gord Dibben]

I would use the Text to Columns method as Ron points out.

But for a VBA solution.................

Sub Negsignleft()
Dim Cell As Range
Dim rng As Range
''move minus sign from right to left on entire worksheet
On Error Resume Next
Set rng = ActiveSheet.Cells. _
SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
For Each Cell In rng
If IsNumeric(Cell.Value) Then
Cell.Value = CDbl(Cell.Value) * 1
End If
Next Cell
End Sub


Gord Dibben MS Excel MVP