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


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=556349

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


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=556349

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"
wrote in message
news:edwardpestian.2a3rqp_1151482204.4269@excelfor um-nospam.com...

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


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile:
http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=556349

Thanks Bob.

ep


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=556349