Driving me crazy

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi,
I have to import sequential files repeatly. The problem
is that the amounts on these files are specified with the
sign in the last character. Excel does not recognize them
as negative numbers (1,234-). The fields vary in length. I
have been going thru and adding the sign to the front of
each number so I can perform numeric functions.
Does anyone know of an easier way to convert these ?

Thanks,
Steve
 
=IF(ISNUMBER(A1),A1,LEFT(A1,FIND("-",A1)-1)*-1) with your
number 1234- in A1 put formula in A2.

HTH
GerryK
 
One way is with a formula, depending on how they look (they are recognized
as text), I am not sure, but if A1 looks like
"1,124-"

you can use
=IF(RIGHT(A1,1)="-",-SUBSTITUTE(A1,"-",""),--(A1))
 
Steve

If you are running XL2002 or 2003 you can do this through Data>Text to Columns

Select the range of cells then Data>Text to Columns>Fixed>Next>Advanced> check
"trailing minus for negative numbers">Finish.

If earlier version of Excel, you cannot Format, you have to switch the sign
using VBA or a helper column with this formula.

If data starts in A1, insert a column and enter this:

=IF(ISERROR(A1*1),LEFT(A1,LEN(A1)-1)*-1,A1)

Copy down as needed.

VBA Method follows.................

Sub ChangeSign()
Dim cell As Range
''to move a - sign from right to left in Column A
On Error Resume Next
For Each cell In Range(Cells(1, 1), _
Cells(Rows.Count, 1).End(xlUp)). _
SpecialCells(xlConstants, xlTextValues)
If Right(Trim(cell.Value), 1) = "-" Then
cell.Value = CDbl(cell.Value)
End If
Next
On Error GoTo 0
End Sub

Gord Dibben Excel MVP
 
Back
Top