Swap trailing minus to leading
 

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?

Swap trailing minus to leading
 

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.
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk


"Curt" wrote:

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?

Swap trailing minus to leading
 

You could try the following using the VBA route:

Sub MoveMinus()

Dim rngCell As Range
Dim lngLastRow As Long
Dim lngLastCol As Long
Dim wksSheet As Worksheet

Application.ScreenUpdating = False
Set wksSheet = ActiveSheet

With wksSheet
'Get last row and column populated
lngLastRow = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows,
xlPrevious).Row
lngLastCol = .Cells.Find("*", .Range("A1"), xlFormulas, ,
xlByColumns, xlPrevious).Column

For Each rngCell In .Range(.Cells(1, 1), .Cells(lngLastRow,
lngLastCol))
If Right(rngCell.Value, 1) = "-" Then
rngCell.Value = "-" & Left(rngCell.Value, Len(rngCell.Value)
- 1)
End If
Next
End With

Application.ScreenUpdating = True

End Sub


Regards,

Chris



"Curt" wrote:

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?

Swap trailing minus to leading
 

On Fri, 17 Oct 2008 07:41:00 -0700, Curt
wrote:

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

Swap trailing minus to leading
 

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

On Fri, 17 Oct 2008 07:41:00 -0700, Curt
wrote:

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?