Remember Me?

#1
November 26th 04, 04:33 PM
 Scott Posts: n/a
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?

#2
November 26th 04, 05:23 PM
 Norman Jones Posts: n/a

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?

#3
November 26th 04, 06:00 PM
 Myrna Larson Posts: n/a

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?

#4
November 26th 04, 09:17 PM
 Gord Dibben Posts: n/a

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?

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Kent Charts and Charting in Excel 2 December 17th 04 12:13 AM AIF_GoofyDo2 Charts and Charting in Excel 0 December 6th 04 01:19 AM Robboo Charts and Charting in Excel 1 November 27th 04 05:49 PM Glenda Excel Discussion (Misc queries) 3 November 26th 04 02:06 PM Duplicateman Excel Discussion (Misc queries) 6 November 26th 04 08:40 AM

All times are GMT +1. The time now is 09:43 AM.