Minus figure translation - help

[shaddy]

I have to cut and paste data from an external application (unknown
name) which reflects minus numbers as: 123- When i put this type of
figure into my table and rank the column, rank will not recognise it
as a minus - does anyone know how i can configure my receiving excel
workbook to convert or accept this 123- as either (123) or -123 ??
MTIA Keith C

 

[JMay]

Assuming your data is in A1:A1000 -- It's not that pretty, but in cell B1
enter (and copy down):

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

 

[JMay]

Of course have a backup of your file before trying all this and of course,
afterwards Copy B1:B1000 and do a Paste-Special Values to itself; then you
can delete Col A
Careful though as you do these things.

 

[J.E. McGimpsey]

You can run this macro to convert the values in place:

Public Sub ConvertPostNegatives()
Dim cell As Range

On Error Resume Next
For Each cell In ActiveSheet.Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
cell.Value = CDbl(cell.Value)
Next cell
On Error GoTo 0
End Sub