Ending Negative Sign

  • Thread starter Thread starter edwardpestian
  • Start date Start date
E

edwardpestian

I have figures that I copy and paste from AS400 into an Excel worksheet.
The figures get pasted as text with a minus sign after the number for
negative values: 2340-. I need to convert these numbers to <2340>
format.

I simply need a formula that says if this range has values with "-" at
the end of the number, then format <xxxx>, otherwise format as +xxxx.

Thanks.

ep
 
hi,

try to convince sbd. to bring the - sign on the other side.

otherwise, that'll do:

=if(right(a1,1)="-", -value(left(a1, len(a1)-1)), value(a1))

arno
 
It works, but not quite as I need it to. I would like the formula to
refer to a range of cells - for example A1:A10. When data is entered
into these cells, it automatically converts the data to the correct
format (In the same cell that the data was entered).

Thanks again.

ep
 
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10"
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
If Right(cell.Value, 1) = "-" Then
cell.Value = Left(cell.Value, Len(cell.Value) - 1) * -1
cell.NumberFormat = "0;<0>"
End If
Next cell
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"edwardpestian" <[email protected]>
wrote in message
news:[email protected]...
 
Back
Top