Converting text to numbers
I have a data file I'm reformatting that has all the negative numbers with
the negative sign on the right side thereby Excel sees these as text. I used a formula last week that converts these to the correct negative number but seemed to have misplaced the formula. Anyone help me with this trick? |
Hi Scott'
One way: =LEFT(A1,LEN(A1)-1)*-1 --- Regards, Norman "Scott" wrote in message ... I have a data file I'm reformatting that has all the negative numbers with the negative sign on the right side thereby Excel sees these as text. I used a formula last week that converts these to the correct negative number but seemed to have misplaced the formula. Anyone help me with this trick? |
Assuming you have a mix of both positive and negative numbers, in the helper
column use this formula: =IF(RIGHT(A1)="-",LEFT(A1,LEN(A1)-1)*-1,A1) On Fri, 26 Nov 2004 08:33:03 -0800, "Scott" wrote: I have a data file I'm reformatting that has all the negative numbers with the negative sign on the right side thereby Excel sees these as text. I used a formula last week that converts these to the correct negative number but seemed to have misplaced the formula. Anyone help me with this trick? |
Scott
If using Excel 2002 or 2003........ DataText to ColumnsNextNextAdvanced Check "trailing minus for negative numbers" Macro............ 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 OR for entire sheet........... 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) End If Next Cell End Sub Gord Dibben Excel MVP On Fri, 26 Nov 2004 08:33:03 -0800, "Scott" wrote: I have a data file I'm reformatting that has all the negative numbers with the negative sign on the right side thereby Excel sees these as text. I used a formula last week that converts these to the correct negative number but seemed to have misplaced the formula. Anyone help me with this trick? |
All times are GMT +1. The time now is 02:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com